﻿using CIMCService;
using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Web.Configuration;
using System.Web.Script.Serialization;
using System.Web.Script.Services;
using System.Web.Services;
using System.Web.UI.MobileControls;

namespace WebApplication1
{

    /// <summary>
    /// WebService1 的摘要说明
    /// </summary>
    [WebService(Namespace = "http://tempuri.org/login/")]
    [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
    [ScriptService]
    public class WebService1 : System.Web.Services.WebService
    {
        //声明通用的类，一些通用的函数
        public static common_class g_class;
        public static string connectionString = WebConfigurationManager.ConnectionStrings["MyDatabaseConnection"].ConnectionString;
        //public static string g_usespace = ConfigurationManager.AppSettings["userspace"];


        // 登录接口
        [WebMethod]
        public string Login(string username, string password, string inv)
        {
            string connectionString = WebConfigurationManager.ConnectionStrings["MyDatabaseConnection"].ConnectionString;
            LogHelpter.AddLog("调用接口【Login】");
            LogHelpter.AddLog("Login username=" + username);
            LogHelpter.AddLog("Login password=" + password);

            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();

                string query = "select user_name,user_password,department from WMS.sys_user where user_id = @Username and inv_org=@Invorg and user_password = @Password";


                using (SqlCommand command = new SqlCommand(query, connection))
                {
                    command.Parameters.AddWithValue("@Username", username);
                    command.Parameters.AddWithValue("@Invorg", inv);
                    // 处理密码，这里使用与你提供的代码相同的逻辑
                    g_class = new common_class();
                    string hashedPassword = g_class.comm_getMd5Hash(username + password + "~RFID~");
                    // 打印处理后的密码 
                    //Console.WriteLine("Processed Password: " + hashedPassword);
                    command.Parameters.AddWithValue("@Password", hashedPassword);

                    using (SqlDataAdapter adapter = new SqlDataAdapter(command))
                    {
                        DataTable dataTable = new DataTable();
                        adapter.Fill(dataTable);

                        if (dataTable.Rows.Count == 1)
                        {
                            Dictionary<string, object> loginResult = new Dictionary<string, object>();
                            loginResult["code"] = 200;
                            loginResult["data"] = "登录成功";
                            loginResult["username"] = username;
                            loginResult["Password"] = hashedPassword;

                            return new System.Web.Script.Serialization.JavaScriptSerializer().Serialize(loginResult);
                        }
                        else
                        {

                            Dictionary<string, object> Result = new Dictionary<string, object>();
                            Result["code"] = 500;
                            Result["data"] = "登录失败";
                            Result["ess"] = hashedPassword;

                            return new System.Web.Script.Serialization.JavaScriptSerializer().Serialize(Result);
                        }
                    }
                }
            }

        }

        [WebMethod]
        public string UpdateApp(string s_inv, string versionName, string versionCode)
        {
            LogHelpter.AddLog("调用接口【UpdateApp】");
            string str_sql = String.Format(@" 
                                select top 1
                                inv_org,
                                ver_code,
                                ver_name,
                                update_title,
                                update_content,
                                update_url
                                from wms.sys_update_app
                                where 1=1 
                                AND inv_org = '{0}' 
                                ORDER BY ver_code desc  ",
                                s_inv);

            try
            {

                DataTable dt = OpDB.GetDataTable(connectionString, str_sql);
                if (dt != null && dt.Rows.Count > 0)
                {
                    string ver_code = dt.Rows[0]["ver_code"].ToString().Trim();
                    string ver_name = dt.Rows[0]["ver_name"].ToString().Trim();
                    string update_title = dt.Rows[0]["update_title"].ToString().Trim();
                    string update_content = dt.Rows[0]["update_content"].ToString().Trim();
                    string update_url = dt.Rows[0]["update_url"].ToString().Trim();

                    var result = new
                    {
                        code = 200,
                        data = "查询成功",
                        s_inv = s_inv,
                        ver_code = ver_code,
                        ver_name = ver_name,
                        update_title = update_title,
                        update_content = update_content,
                        update_url = update_url,
                    };
                    return JsonConvert.SerializeObject(result);



                }
                else
                {
                    var result = new
                    {
                        code = 202,
                        data = "未查询到符合条件的记录",
                        s_inv = s_inv
                    };

                    return JsonConvert.SerializeObject(result);
                }



            }
            catch (Exception ex)
            {
                var result = new
                {
                    code = 500,
                    data = "查询失败: " + ex.Message, // "Query failed"
                    s_inv = s_inv
                };
                return JsonConvert.SerializeObject(result);
            }
        }

        // 上架确认列表（根据库位显示单号）
        [WebMethod]
        public string GroundingLocArea(string inv)
        {
            LogHelpter.AddLog("调用接口【GroundingLocArea】");
            string connectionString = WebConfigurationManager.ConnectionStrings["MyDatabaseConnection"].ConnectionString;
            Dictionary<string, object> result = new Dictionary<string, object>();

            //if (string.IsNullOrEmpty(inv))
            //{
            //    result["code"] = 500;
            //    result["data"] = "收货单获取失败";
            //    result["asn_no"] = asnNo;
            //    return new JavaScriptSerializer().Serialize(result);
            //}

            List<Dictionary<string, object>> itemsList = new List<Dictionary<string, object>>();

            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                //string query = "SELECT * FROM WMS.asn_putaway WHERE inv_org = @InvOrg AND putaway_status = '0' AND asn_no = @AsnNo";
                //string query = " select a.asn_line_no,a.receive_line_no,a.sku,a.putaway_qty_m,a.suggest_location,a.fact_location,b.supply,isnull(c.consignor_name,'') consignor_name from WMS.asn_putaway a, WMS.asn_order_header b, WMS.mst_customer c  where a.inv_org= @InvOrg  and a.asn_no =@AsnNo and a.putaway_status='0' and a.inv_org = b.inv_org and a.consignor = b.consignor and a.asn_no = b.asn_no and b.inv_org *= c.inv_org and ltrim(rtrim(b.supply)) *= ltrim(rtrim(c.consignor)) order by a.asn_line_no,a.receive_line_no";
                //string query = " select a.asn_line_no,a.receive_line_no,a.sku,a.putaway_qty_m,a.suggest_location,a.fact_location,b.supply,nvl(c.consignor_name,'') consignor_name from WMS.asn_putaway a, WMS.asn_order_header b, WMS.mst_customer c  where a.inv_org=@InvOrg  and a.asn_no = @AsnNo  and a.putaway_status='0' and a.inv_org = b.inv_org and a.consignor = b.consignor and a.asn_no = b.asn_no and b.inv_org = c.inv_org (+) and trim(b.supply) = trim(c.consignor (+))  order by a.asn_line_no,a.receive_line_no";
                string query = "select distinct query_location from ( " +
                    "SELECT " +
                    "a.asn_line_no, " +
                    "a.receive_line_no, " +
                    "a.sku, " +
                    "d.sku_name, " +
                    "a.putaway_qty_m, " +


                    "d.fix_location, " +  // A位库位
                    "a.suggest_location, " + // 建议库位
                    "CASE  WHEN a.suggest_location = 'UNKNOWN' THEN  fix_location ELSE a.suggest_location END AS query_location," +     // 查询库位
                    "LEFT(CASE  WHEN a.suggest_location = 'UNKNOWN' THEN fix_location ELSE a.suggest_location END, 4) AS loc_area ," + // 库位区域编号（库位前4位）
                    "a.fact_location, " +
                    "b.supply, " +
                    "ISNULL(c.consignor_name, '') AS consignor_name " +
                    "FROM " +
                    "WMS.asn_putaway a " +
                    "INNER JOIN WMS.asn_order_header b ON a.inv_org = b.inv_org " +
                    "                                   AND a.consignor = b.consignor " +
                    "                                   AND a.asn_no = b.asn_no " +
                    "LEFT JOIN WMS.mst_customer c ON b.inv_org = c.inv_org " +
                    "                               AND LTRIM(RTRIM(b.supply)) = LTRIM(RTRIM(c.consignor)) " +
                    "LEFT JOIN WMS.mst_sku d ON a.inv_org = d.inv_org AND a.consignor = d.consignor AND a.sku = d.sku  " +
                    "WHERE " +
                    "a.inv_org = @InvOrg " +
                    // "AND a.asn_no = @AsnNo " + // 改成根据库位查询（单号查询功能去掉）
                    //"AND LEFT(CASE  WHEN a.suggest_location = 'UNKNOWN' THEN fix_location ELSE a.suggest_location END, 4) = @locArea ," + // 库位区域编号（库位前4位）
                    "AND a.putaway_status = '0' " +

                    ") as t1";

                using (SqlCommand command = new SqlCommand(query, connection))
                {
                    command.Parameters.AddWithValue("@InvOrg", inv);
                    //command.Parameters.AddWithValue("@AsnNo", asnNo);
                    //command.Parameters.AddWithValue("@locArea", locArea);

                    try
                    {
                        connection.Open();
                        SqlDataReader reader = command.ExecuteReader();

                        while (reader.Read())
                        {
                            Dictionary<string, object> itemDetails = new Dictionary<string, object>();
                            for (int i = 0; i < reader.FieldCount; i++)
                            {

                                // 检查是否为 sku 列，然后输出它的值
                                if (reader.GetName(i) == "query_location")
                                {
                                    itemDetails[reader.GetName(i)] = reader[i];
                                }
                                //if (reader.GetName(i) == "asn_line_no")
                                //{
                                //    itemDetails[reader.GetName(i)] = reader[i];
                                //}
                                //if (reader.GetName(i) == "receive_line_no")
                                //{
                                //    itemDetails[reader.GetName(i)] = reader[i];
                                //}
                                //if (reader.GetName(i) == "sku")
                                //{
                                //    itemDetails[reader.GetName(i)] = reader[i];
                                //}
                                //if (reader.GetName(i) == "sku_name")
                                //{
                                //    itemDetails[reader.GetName(i)] = reader[i];
                                //}
                                //if (reader.GetName(i) == "putaway_qty_m")
                                //{
                                //    itemDetails[reader.GetName(i)] = reader[i];
                                //}
                                //if (reader.GetName(i) == "suggest_location")
                                //{
                                //    itemDetails[reader.GetName(i)] = reader[i];
                                //}
                                //if (reader.GetName(i) == "fact_location")
                                //{
                                //    itemDetails[reader.GetName(i)] = reader[i];
                                //}
                                //if (reader.GetName(i) == "supply")
                                //{
                                //    itemDetails[reader.GetName(i)] = reader[i];
                                //}
                                //if (reader.GetName(i) == "consignor_name")
                                //{
                                //    itemDetails[reader.GetName(i)] = reader[i];
                                //}
                            }
                            itemsList.Add(itemDetails);
                        }

                        reader.Close();

                        if (itemsList.Count > 0)
                        {
                            result["code"] = 200;
                            result["data"] = "收到商品信息";
                            //result["asn_no"] = asnNo;
                            result["items"] = itemsList;
                        }
                        else
                        {
                            result["code"] = 202;
                            //result["data"] = "收货单 " + asnNo + " 没有待上架的商品";
                            //result["asn_no"] = asnNo;
                        }
                    }
                    catch (Exception ex)
                    {
                        result["code"] = 500;
                        //result["data"] = "获取上架单据信息失败: " + ex.Message;
                        //result["asn_no"] = asnNo;
                    }
                }
            }

            return new JavaScriptSerializer().Serialize(result);
        }

        // 上架确认列表
        [WebMethod]
        public string Grounding(string locArea, string inv)
        {
            LogHelpter.AddLog("调用接口【Grounding】");
            string connectionString = WebConfigurationManager.ConnectionStrings["MyDatabaseConnection"].ConnectionString;
            Dictionary<string, object> result = new Dictionary<string, object>();

            if (string.IsNullOrEmpty(locArea))
            {
                result["code"] = 500;
                result["data"] = "收货单获取失败";
                result["asn_no"] = locArea;
                return new JavaScriptSerializer().Serialize(result);
            }

            List<Dictionary<string, object>> itemsList = new List<Dictionary<string, object>>();

            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                //string query = "SELECT * FROM WMS.asn_putaway WHERE inv_org = @InvOrg AND putaway_status = '0' AND asn_no = @AsnNo";
                //string query = " select a.asn_line_no,a.receive_line_no,a.sku,a.putaway_qty_m,a.suggest_location,a.fact_location,b.supply,isnull(c.consignor_name,'') consignor_name from WMS.asn_putaway a, WMS.asn_order_header b, WMS.mst_customer c  where a.inv_org= @InvOrg  and a.asn_no =@AsnNo and a.putaway_status='0' and a.inv_org = b.inv_org and a.consignor = b.consignor and a.asn_no = b.asn_no and b.inv_org *= c.inv_org and ltrim(rtrim(b.supply)) *= ltrim(rtrim(c.consignor)) order by a.asn_line_no,a.receive_line_no";
                //string query = " select a.asn_line_no,a.receive_line_no,a.sku,a.putaway_qty_m,a.suggest_location,a.fact_location,b.supply,nvl(c.consignor_name,'') consignor_name from WMS.asn_putaway a, WMS.asn_order_header b, WMS.mst_customer c  where a.inv_org=@InvOrg  and a.asn_no = @AsnNo  and a.putaway_status='0' and a.inv_org = b.inv_org and a.consignor = b.consignor and a.asn_no = b.asn_no and b.inv_org = c.inv_org (+) and trim(b.supply) = trim(c.consignor (+))  order by a.asn_line_no,a.receive_line_no";
                string query = "SELECT " +
                    "a.inv_org, " +
                    "a.consignor, " +
                    "a.asn_no, " +
                    "a.asn_line_no, " +
                    "a.receive_line_no, " +
                    "a.sku, " +
                    "d.sku_name, " +
                    "a.putaway_qty_m, " +
                    "e.qty02 as box_spec, " +

                    "d.fix_location, " +  // A位库位
                    //"a.suggest_location, " + // 建议库位
                    "CASE  WHEN a.suggest_location = 'UNKNOWN' THEN fix_location + '*' ELSE a.suggest_location END AS suggest_location," +     // 查询库位
                    "LEFT(CASE  WHEN a.suggest_location = 'UNKNOWN' THEN fix_location ELSE a.suggest_location END, 4) AS loc_area ," + // 库位区域编号（库位前4位）
                    "a.fact_location, " +
                    "b.supply, " +
                    "ISNULL(c.consignor_name, '') AS consignor_name " +
                    "FROM " +
                    "WMS.asn_putaway a " +
                    "INNER JOIN WMS.asn_order_header b ON a.inv_org = b.inv_org " +
                    "                                   AND a.consignor = b.consignor " +
                    "                                   AND a.asn_no = b.asn_no " +
                    "LEFT JOIN WMS.mst_customer c ON b.inv_org = c.inv_org " +
                    "                               AND LTRIM(RTRIM(b.supply)) = LTRIM(RTRIM(c.consignor)) " +
                    "LEFT JOIN WMS.mst_sku d ON a.inv_org = d.inv_org AND a.consignor = d.consignor AND a.sku = d.sku  " +
                    "LEFT JOIN WMS.mst_pack e ON a.inv_org = e.inv_org AND a.consignor = e.consignor AND a.sku = e.sku " +
                    "WHERE " +
                    "a.inv_org = @InvOrg " +
                    //"AND a.asn_no = @AsnNo " + // 改成根据库位查询（单号查询功能去掉）
                    "AND LEFT(CASE  WHEN a.suggest_location = 'UNKNOWN' THEN fix_location ELSE a.suggest_location END, 4) = @LocArea " +
                    "AND a.putaway_status = '0' " +
                    "ORDER BY " +
                    "a.suggest_location "; // 改成按建议库位排序
                                           //"a.asn_line_no, " +
                                           //"a.receive_line_no";


                using (SqlCommand command = new SqlCommand(query, connection))
                {
                    command.Parameters.AddWithValue("@InvOrg", inv);
                    command.Parameters.AddWithValue("@LocArea", locArea);

                    try
                    {
                        connection.Open();
                        SqlDataReader reader = command.ExecuteReader();

                        while (reader.Read())
                        {
                            Dictionary<string, object> itemDetails = new Dictionary<string, object>();
                            for (int i = 0; i < reader.FieldCount; i++)
                            {

                                // 检查是否为 sku 列，然后输出它的值
                                if (reader.GetName(i) == "asn_no")
                                {
                                    itemDetails[reader.GetName(i)] = reader[i];
                                }
                                if (reader.GetName(i) == "asn_line_no")
                                {
                                    itemDetails[reader.GetName(i)] = reader[i];
                                }
                                if (reader.GetName(i) == "receive_line_no")
                                {
                                    itemDetails[reader.GetName(i)] = reader[i];
                                }
                                if (reader.GetName(i) == "sku")
                                {
                                    itemDetails[reader.GetName(i)] = reader[i];
                                }
                                if (reader.GetName(i) == "sku_name")
                                {
                                    itemDetails[reader.GetName(i)] = reader[i];
                                }
                                if (reader.GetName(i) == "putaway_qty_m")
                                {
                                    itemDetails[reader.GetName(i)] = reader[i];
                                }
                                if (reader.GetName(i) == "box_spec")
                                {
                                    itemDetails[reader.GetName(i)] = reader[i];
                                }
                                if (reader.GetName(i) == "fix_location")
                                {
                                    itemDetails[reader.GetName(i)] = reader[i];
                                }
                                if (reader.GetName(i) == "suggest_location")
                                {
                                    itemDetails[reader.GetName(i)] = reader[i];
                                }
                                if (reader.GetName(i) == "fact_location")
                                {
                                    itemDetails[reader.GetName(i)] = reader[i];
                                }
                                if (reader.GetName(i) == "supply")
                                {
                                    itemDetails[reader.GetName(i)] = reader[i];
                                }
                                if (reader.GetName(i) == "consignor_name")
                                {
                                    itemDetails[reader.GetName(i)] = reader[i];
                                }
                            }
                            itemsList.Add(itemDetails);
                        }

                        reader.Close();

                        if (itemsList.Count > 0)
                        {
                            result["code"] = 200;
                            result["data"] = "收到商品信息";
                            //result["asn_no"] = asnNo;
                            result["items"] = itemsList;
                        }
                        else
                        {
                            result["code"] = 202;
                            result["data"] = "没有待上架的商品";
                            //result["data"] = "收货单 " + asnNo + " 没有待上架的商品";
                            //result["asn_no"] = asnNo;
                        }
                    }
                    catch (Exception ex)
                    {
                        result["code"] = 500;
                        result["data"] = "获取上架单据信息失败: " + ex.Message;
                        //result["asn_no"] = asnNo;
                    }
                }
            }

            return new JavaScriptSerializer().Serialize(result);
        }

        [WebMethod]
        public string GroundingAll(string inv)
        {
            LogHelpter.AddLog("调用接口【GroundingAll】");
            string connectionString = WebConfigurationManager.ConnectionStrings["MyDatabaseConnection"].ConnectionString;
            DataSet ds_temp = new DataSet();

            string query = "SELECT " +
                               "a.asn_no, " +
                               "a.receive_line_no, " +
                               "a.sku, " +
                               "a.putaway_qty_m, " +
                               "d.fix_location, " +  // A位库位
                                "a.suggest_location, " + // 建议库位
                                "CASE  WHEN a.suggest_location = 'UNKNOWN' THEN fix_location ELSE a.suggest_location END AS query_location," +     // 查询库位
                               "a.fact_location, " +
                               "b.supply, " +
                               "ISNULL(c.consignor_name, '') AS consignor_name " +
                               "FROM " +
                               "WMS.asn_putaway a " +
                               "INNER JOIN WMS.asn_order_header b ON a.inv_org = b.inv_org " +
                               "                                   AND a.consignor = b.consignor " +
                               "                                   AND a.asn_no = b.asn_no " +
                               "LEFT JOIN WMS.mst_customer c ON b.inv_org = c.inv_org " +
                               "                               AND LTRIM(RTRIM(b.supply)) = LTRIM(RTRIM(c.consignor)) " +
                               "LEFT JOIN WMS.mst_sku d ON a.inv_org = d.inv_org AND a.consignor = d.consignor AND a.sku = d.sku  " +
                               "WHERE " +
                               "a.inv_org = @InvOrg " +
                               "AND a.putaway_status = '0' " +
                               "ORDER BY " +
                               "a.asn_line_no, " +
                               "a.receive_line_no";


            try
            {
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    using (SqlCommand command = new SqlCommand(query, connection))
                    {
                        command.Parameters.AddWithValue("@InvOrg", inv);

                        connection.Open();
                        SqlDataReader reader = command.ExecuteReader();

                        List<string> skuList = new List<string>(); // 用于存储 SKU 编号

                        if (reader.HasRows)
                        {

                            while (reader.Read())
                            {
                                // 检查并添加 SKU 编号
                                string asnNo = reader["asn_no"].ToString();
                                if (!string.IsNullOrEmpty(asnNo))
                                {
                                    skuList.Add(asnNo);
                                }
                            }

                            reader.Close();

                            Dictionary<string, object> result = new Dictionary<string, object>();
                            result["code"] = 200;
                            result["data"] = "查询成功";
                            result["asn_no"] = skuList; // 返回 SKU 列表

                            return new JavaScriptSerializer().Serialize(result);
                        }
                        else
                        {
                            reader.Close();

                            Dictionary<string, object> result = new Dictionary<string, object>();
                            result["code"] = 202;
                            result["data"] = "未查询到符合条件的订单";
                            result["asn_no"] = new List<string>(); // 返回空 SKU 列表

                            return new JavaScriptSerializer().Serialize(result);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Dictionary<string, object> result = new Dictionary<string, object>();
                result["code"] = 500;
                result["data"] = "查询失败: " + ex.Message;
                result["asn_no"] = new List<string>(); // 返回空 SKU 列表

                return new JavaScriptSerializer().Serialize(result);
            }
        }

        // 上架确认信息（明细信息）
        [WebMethod]
        public string Groundingdata(string asnNo, string inv, string str_lot, string sku,string str_asn_line_no, string str_receive_line_no)
        {
            LogHelpter.AddLog("调用接口【Groundingdata】");
            string connectionString = WebConfigurationManager.ConnectionStrings["MyDatabaseConnection"].ConnectionString;
            Dictionary<string, object> result = new Dictionary<string, object>();

            if (string.IsNullOrEmpty(sku))
            {
                result["code"] = 500;
                result["data"] = "商品信息获取失败";
                result["asn_no"] = asnNo;
                return new JavaScriptSerializer().Serialize(result);
            }

            List<Dictionary<string, object>> itemsList = new List<Dictionary<string, object>>();

            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                //CASE  WHEN a.suggest_location = 'UNKNOWN' THEN fix_location ELSE a.suggest_location END AS query_location,
                // 定义SQL查询语句
                string str_sql = "SELECT a.consignor, a.pallet_no, a.asn_line_no, a.receive_line_no, a.sku, b.sku_name,b.fix_location, c.qty02 as box_spec, " +
                                 "ISNULL(a.putaway_qty_m, 0) AS putaway_qty_m, " +
                                 "a.suggest_location, " +
                                 "b.fix_location, " +  // A位库位
                                 //"a.suggest_location, " +
                                 "CASE WHEN a.suggest_location = 'UNKNOWN' THEN fix_location + '*' ELSE a.suggest_location END AS suggest_location," +     // 查询库位

                                 "a.lot_attribute5, a.lot_attribute6, a.lot_attribute7 " +
                                 "FROM WMS.asn_putaway a " +
                                 "INNER JOIN WMS.mst_sku b ON a.inv_org = b.inv_org AND a.consignor = b.consignor AND a.sku = b.sku " +
                                 "LEFT JOIN WMS.mst_pack C ON a.inv_org = b.inv_org AND a.consignor = b.consignor AND a.sku = c.sku " +
                                 "WHERE a.inv_org = @InvOrg " +
                                 "AND a.asn_no = @AsnNo " +
                                 "AND a.asn_line_no = @AsnLineNo " +
                                 "AND a.receive_line_no = @ReceivelineNo " +
                                 "AND a.sku = @Sku " +
                                 "AND a.lot_attribute5 = @LotAttribute5 " +
                                 "AND a.putaway_status = '0' " +
                                 "ORDER BY a.asn_line_no, a.receive_line_no";


                using (SqlCommand command = new SqlCommand(str_sql, connection))
                {
                    // 传递参数到SQL查询
                    command.Parameters.AddWithValue("@InvOrg", inv);
                    command.Parameters.AddWithValue("@AsnNo", asnNo);
                    command.Parameters.AddWithValue("@AsnLineNo", str_asn_line_no);
                    command.Parameters.AddWithValue("@ReceivelineNo", str_receive_line_no);
                    command.Parameters.AddWithValue("@Sku", sku);
                    command.Parameters.AddWithValue("@LotAttribute5", str_lot);

                    try
                    {
                        connection.Open();
                        SqlDataReader reader = command.ExecuteReader();

                        while (reader.Read())
                        {
                            Dictionary<string, object> itemDetails = new Dictionary<string, object>();
                            for (int i = 0; i < reader.FieldCount; i++)
                            {

                                // 检查是否为 sku 列，然后输出它的值
                                if (reader.GetName(i) == "lot_attribute5")
                                {
                                    itemDetails[reader.GetName(i)] = reader[i];
                                }
                                if (reader.GetName(i) == "pallet_no")
                                {
                                    itemDetails[reader.GetName(i)] = reader[i];
                                }
                                if (reader.GetName(i) == "lot_attribute6")
                                {
                                    itemDetails[reader.GetName(i)] = reader[i];
                                }
                                if (reader.GetName(i) == "lot_attribute7")
                                {
                                    itemDetails[reader.GetName(i)] = reader[i];
                                }
                                if (reader.GetName(i) == "asn_line_no")
                                {
                                    itemDetails[reader.GetName(i)] = reader[i];
                                }
                                if (reader.GetName(i) == "receive_line_no")
                                {
                                    itemDetails[reader.GetName(i)] = reader[i];
                                }
                                if (reader.GetName(i) == "sku_name")
                                {
                                    itemDetails[reader.GetName(i)] = reader[i];
                                }
                                if (reader.GetName(i) == "box_spec")
                                {
                                    itemDetails[reader.GetName(i)] = reader[i];
                                }
                                if (reader.GetName(i) == "putaway_qty_m")
                                {
                                    itemDetails[reader.GetName(i)] = reader[i];
                                }
                                if (reader.GetName(i) == "suggest_location")
                                {
                                    itemDetails[reader.GetName(i)] = reader[i];
                                }
                                if (reader.GetName(i) == "fix_location")
                                {
                                    itemDetails[reader.GetName(i)] = reader[i];
                                }
                                if (reader.GetName(i) == "consignor")
                                {
                                    itemDetails[reader.GetName(i)] = reader[i];
                                }
                                if (reader.GetName(i) == "pallet_no")
                                {
                                    itemDetails[reader.GetName(i)] = reader[i];
                                }
                                if (reader.GetName(i) == "sku")
                                {
                                    itemDetails[reader.GetName(i)] = reader[i];
                                }
                            }
                            itemsList.Add(itemDetails);
                        }

                        reader.Close();

                        if (itemsList.Count > 0)
                        {
                            result["code"] = 200;
                            result["data"] = "收到商品信息";
                            result["asn_no"] = asnNo;
                            result["items"] = itemsList;
                        }
                        else
                        {
                            result["code"] = 202;
                            result["data"] = "收货单 " + asnNo + " 没有待上架的商品";
                            result["asn_no"] = asnNo;
                        }
                    }
                    catch (Exception ex)
                    {
                        result["code"] = 500;
                        result["data"] = "获取上架单据信息失败: " + ex.Message;
                        result["asn_no"] = asnNo;
                    }
                }
            }

            return new JavaScriptSerializer().Serialize(result);
        }


        //判断上架库位是否有效
        [WebMethod]
        public string Sql_exec_proc_rf_judgeloc(string s_org, string s_consignor, string s_asn_no, int i_lineno, int i_recline, string s_pallet, string s_loc)
        {
            LogHelpter.AddLog("调用接口【Sql_exec_proc_rf_judgeloc】");
            SqlCommand command;
            command = new SqlCommand("WMS.SP_SQL_JUDGE_LOC");
            SqlDatabaseController SqldbCenter = new SqlDatabaseController();
            try
            {
                SqldbCenter.OpenConnection();
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add("@sp_org", SqlDbType.Char, 15);
                command.Parameters["@sp_org"].Value = s_org;
                command.Parameters.Add("@sp_csgor", SqlDbType.Char, 15);
                command.Parameters["@sp_csgor"].Value = s_consignor;
                command.Parameters.Add("@sp_asn", SqlDbType.Char, 20);
                command.Parameters["@sp_asn"].Value = s_asn_no;
                command.Parameters.Add("@sp_lineno", SqlDbType.Int);
                command.Parameters["@sp_lineno"].Value = i_lineno;
                command.Parameters.Add("@sp_recno", SqlDbType.Int);
                command.Parameters["@sp_recno"].Value = i_recline;
                command.Parameters.Add("@sp_pallet", SqlDbType.Char, 15);
                command.Parameters["@sp_pallet"].Value = s_pallet;
                command.Parameters.Add("@sp_loc", SqlDbType.Char, 20);
                command.Parameters["@sp_loc"].Value = s_loc;
                command.Parameters.Add("@sp_out", SqlDbType.VarChar, 50);
                command.Parameters["@sp_out"].Direction = ParameterDirection.Output;
                SqldbCenter.ExecuteNonQuery(command);
            }
            catch (System.Exception)
            {
                throw;
            }
            finally
            {
                SqldbCenter.CloseConnection();
            }
            return command.Parameters["@sp_out"].Value.ToString().Trim();
        }


        //上架确认
        [WebMethod]
        public string Sql_exec_proc_rf_putawaycimc(string s_org, DataSet ds_data, string str_prcloc, string s_user, string json)
        {
            LogHelpter.AddLog("调用接口【Sql_exec_proc_rf_putawaycimc】str_prcloc=" + str_prcloc + ",s_user=" + s_user);

            string str_sql;
            string str_ls_middle;
            SqlCommand command;
            command = new SqlCommand();
            SqlDatabaseController SqldbCenter = new SqlDatabaseController();
            str_ls_middle = "";
            DataSet dataSet = JsonConvert.DeserializeObject<DataSet>(json);
            // 如果JSON字符串不包含DataSet的完整结构（如没有明确的"Table"名称或没有"Tables"集合），则可能需要手动构建DataSet
            if (dataSet.Tables.Count == 0)
            {
                // 手动创建DataTable并添加到DataSet中
                DataTable dataTable = JsonConvert.DeserializeObject<DataTable>(json.Replace("Table", ""));
                dataSet.Tables.Add(dataTable);
            }

            // 现在dataSet包含了解析后的数据

            ds_data = dataSet;

            try
            {
                //先修改实际货位
                SqldbCenter.OpenConnection();
                SqldbCenter.BeginTransaction();
                //然后调用上架确认的存储过程
                for (int i = 0; i < ds_data.Tables[0].Rows.Count; i++)
                {
                    str_sql = "update WMS.asn_putaway set fact_location = '" + str_prcloc + "' where inv_org = '" + s_org + "' and pallet_no = '" + ds_data.Tables[0].Rows[i]["pallet_no"].ToString().Trim() +
                        "' and consignor = '" + ds_data.Tables[0].Rows[i]["consignor"].ToString().Trim() + "' and asn_no = '" + ds_data.Tables[0].Rows[i]["asn_no"].ToString().Trim() + "' and " +
                        " asn_line_no = " + ds_data.Tables[0].Rows[i]["asn_line_no"].ToString().Trim() + " and receive_line_no = " + ds_data.Tables[0].Rows[i]["receive_line_no"].ToString().Trim() + " and " +
                        " putaway_status = '0'";
                    command.CommandType = CommandType.Text;
                    command.CommandText = str_sql;
                    SqldbCenter.ExecuteNonQuery(command);

                    command.CommandType = CommandType.StoredProcedure;
                    command.CommandText = "WMS.SP_SQL_PA_CONFIRM";
                    command.Parameters.Add("@sp_org", SqlDbType.Char, 15);
                    command.Parameters["@sp_org"].Value = s_org;
                    command.Parameters.Add("@sp_csgor", SqlDbType.Char, 15);
                    command.Parameters["@sp_csgor"].Value = ds_data.Tables[0].Rows[i]["consignor"].ToString().Trim();
                    command.Parameters.Add("@sp_asn", SqlDbType.Char, 20);
                    command.Parameters["@sp_asn"].Value = ds_data.Tables[0].Rows[i]["asn_no"].ToString().Trim();
                    command.Parameters.Add("@sp_lineno", SqlDbType.Int);
                    command.Parameters["@sp_lineno"].Value = System.Convert.ToInt32(ds_data.Tables[0].Rows[i]["asn_line_no"].ToString().Trim());
                    command.Parameters.Add("@sp_recno", SqlDbType.Int);
                    command.Parameters["@sp_recno"].Value = System.Convert.ToInt32(ds_data.Tables[0].Rows[i]["receive_line_no"].ToString().Trim());
                    command.Parameters.Add("@sp_pallet", SqlDbType.Char, 15);
                    command.Parameters["@sp_pallet"].Value = ds_data.Tables[0].Rows[0]["pallet_no"].ToString().Trim();
                    command.Parameters.Add("@sp_user", SqlDbType.Char, 10);
                    command.Parameters["@sp_user"].Value = s_user;
                    command.Parameters.Add("@sp_method", SqlDbType.Char, 1);
                    command.Parameters["@sp_method"].Value = "1";
                    command.Parameters.Add("@sp_out", SqlDbType.VarChar, 50);
                    command.Parameters["@sp_out"].Direction = ParameterDirection.Output;
                    SqldbCenter.ExecuteNonQuery(command);
                    if (command.Parameters["@sp_out"].Value.ToString().Trim() != "0")
                    {
                        str_ls_middle = str_ls_middle + command.Parameters["@sp_out"].Value.ToString().Trim();
                    }
                    command.Parameters.Clear();
                }
                if (str_ls_middle.Trim() == "")
                {
                    SqldbCenter.Commit();
                }
                else
                {
                    SqldbCenter.Rollback();
                }

            }
            catch (System.Exception)
            {

                throw;
            }
            finally
            {
                SqldbCenter.CloseConnection();
            }
            return str_ls_middle.Trim();
        }

        // 收货确认（显示下拉列表）
        [WebMethod]
        public string Sotemplist(string asnNo, string inv)
        {
            LogHelpter.AddLog("调用接口【Sotemplist】");
            string connectionString = WebConfigurationManager.ConnectionStrings["MyDatabaseConnection"].ConnectionString;
            DataSet ds_temp = new DataSet();

            string str_sql = "select a.customer_order_no, a.asn_no, a.consignor, a.arrive_plan_day, a.supply, " +
                             "case a.arrive_status when '0' then '未到' when '1' then '部分' else '' end arrive_status " +
                             "from WMS.asn_order_header a " +
                             "where a.inv_org = @InvOrg and a.standby_4 = 1 " + 
                             //"where a.inv_org = @InvOrg and a.standby_4 = 1 and a.arrive_status in ('0','1') " +
                             "AND a.arrive_status in ('0','1') " +
                             "AND a.asn_status = '0'  " + // 0-正常 1-关闭
                             "and a.asn_type in ('0','3') and a.eas_order = @EasOrder";

            try
            {
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    using (SqlCommand command = new SqlCommand(str_sql, connection))
                    {
                        command.Parameters.AddWithValue("@InvOrg", inv);
                        command.Parameters.AddWithValue("@EasOrder", asnNo);

                        connection.Open();
                        SqlDataReader reader = command.ExecuteReader();

                        if (reader.HasRows)
                        {
                            List<Dictionary<string, object>> itemsList = new List<Dictionary<string, object>>();

                            while (reader.Read())
                            {
                                Dictionary<string, object> itemDetails = new Dictionary<string, object>();

                                for (int i = 0; i < reader.FieldCount; i++)
                                {
                                    // 检查是否为 sku 列，然后输出它的值
                                    if (reader.GetName(i) == "asn_no")
                                    {
                                        itemDetails[reader.GetName(i)] = reader[i];
                                    }
                                    if (reader.GetName(i) == "arrive_plan_day")
                                    {
                                        itemDetails[reader.GetName(i)] = reader[i];
                                    }
                                    if (reader.GetName(i) == "arrive_status")
                                    {
                                        itemDetails[reader.GetName(i)] = reader[i];
                                    }
                                    if (reader.GetName(i) == "consignor")
                                    {
                                        itemDetails[reader.GetName(i)] = reader[i];
                                    }
                                    if (reader.GetName(i) == "customer_order_no")
                                    {
                                        itemDetails[reader.GetName(i)] = reader[i];
                                    }
                                    if (reader.GetName(i) == "supply")
                                    {
                                        itemDetails[reader.GetName(i)] = reader[i];
                                    }
                                }

                                itemsList.Add(itemDetails);
                            }

                            reader.Close();

                            Dictionary<string, object> result = new Dictionary<string, object>();
                            result["code"] = 200;
                            result["data"] = "查询成功";
                            result["asn_no"] = asnNo;
                            result["items"] = itemsList;

                            // 查询供货商信息
                            string supplierSql = "select consignor_name from WMS.mst_customer where inv_org = @InvOrg and consignor = @Supply";

                            using (SqlCommand commandSupplier = new SqlCommand(supplierSql, connection))
                            {
                                commandSupplier.Parameters.AddWithValue("@InvOrg", inv);
                                commandSupplier.Parameters.AddWithValue("@Supply", itemsList[0]["supply"].ToString().Trim());

                                SqlDataReader readerSupplier = commandSupplier.ExecuteReader();

                                if (readerSupplier.HasRows)
                                {
                                    while (readerSupplier.Read())
                                    {
                                        result["consignor_name"] = readerSupplier["consignor_name"].ToString();
                                        result["supply"] = itemsList[0]["supply"].ToString().Trim();
                                    }
                                }
                                else
                                {
                                    result["consignor_name"] = "";
                                    result["supply"] = "";

                                }

                                readerSupplier.Close();
                            }

                            return new JavaScriptSerializer().Serialize(result);
                        }
                        else
                        {
                            reader.Close();

                            Dictionary<string, object> result = new Dictionary<string, object>();
                            result["code"] = 202;
                            result["data"] = "未查询到符合条件的订单";
                            result["asn_no"] = asnNo;

                            return new JavaScriptSerializer().Serialize(result);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Dictionary<string, object> result = new Dictionary<string, object>();
                result["code"] = 500;
                result["data"] = "查询失败: " + ex.Message;
                result["asn_no"] = asnNo;


                return new JavaScriptSerializer().Serialize(result);
            }
        }

        // 收货（显示所有单据）
        [WebMethod]
        public string SotemplistAll(string inv)
        {
            LogHelpter.AddLog("调用接口【SotemplistAll】");
            string connectionString = WebConfigurationManager.ConnectionStrings["MyDatabaseConnection"].ConnectionString;
            DataSet ds_temp = new DataSet();

            string str_sql = "SELECT a.customer_order_no,a.asn_no, a.consignor, a.arrive_plan_day, a.supply, " +
                             "CASE a.arrive_status WHEN '0' THEN '未到' WHEN '1' THEN '部分' ELSE '' END arrive_status " +
                             "FROM WMS.asn_order_header a " +
                             "WHERE a.inv_org = @InvOrg AND a.standby_4 = 1 "+ 
                             "AND a.arrive_status in ('0','1') "+
                             "AND a.asn_status = '0'  " +
                             "AND a.asn_type IN ('0', '3') " +
                             " order by a.asn_no desc ";

            try
            {
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    using (SqlCommand command = new SqlCommand(str_sql, connection))
                    {
                        command.Parameters.AddWithValue("@InvOrg", inv);

                        connection.Open();
                        SqlDataReader reader = command.ExecuteReader();

                        List<string> skuList = new List<string>(); // 用于存储 SKU 编号

                        if (reader.HasRows)
                        {

                            while (reader.Read())
                            {
                                // 检查并添加 SKU 编号
                                string asnNo = reader["asn_no"].ToString();
                                if (!string.IsNullOrEmpty(asnNo))
                                {
                                    skuList.Add(asnNo);
                                }
                            }

                            reader.Close();

                            Dictionary<string, object> result = new Dictionary<string, object>();
                            result["code"] = 200;
                            result["data"] = "查询成功";
                            result["asn_no"] = skuList; // 返回 SKU 列表

                            return new JavaScriptSerializer().Serialize(result);
                        }
                        else
                        {
                            reader.Close();

                            Dictionary<string, object> result = new Dictionary<string, object>();
                            result["code"] = 202;
                            result["data"] = "未查询到符合条件的订单";
                            result["asn_no"] = new List<string>(); // 返回空 SKU 列表

                            return new JavaScriptSerializer().Serialize(result);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Dictionary<string, object> result = new Dictionary<string, object>();
                result["code"] = 500;
                result["data"] = "查询失败: " + ex.Message;
                result["asn_no"] = new List<string>(); // 返回空 SKU 列表

                return new JavaScriptSerializer().Serialize(result);
            }
        }


        [WebMethod]
        public string DisplayDetail(string asnNo, string inv )
        {
            LogHelpter.AddLog("调用接口【DisplayDetail】");
            string connectionString = WebConfigurationManager.ConnectionStrings["MyDatabaseConnection"].ConnectionString;
            DataSet ds_temp = new DataSet();
            // CASE  WHEN a.suggest_location = 'UNKNOWN' THEN fix_location ELSE a.suggest_location END AS query_location,
            string str_sql = "SELECT a.asn_line_no, a.sku,b.barcode,c.barcode_list," +
                             "b.fix_location, " +
                             "CASE  WHEN a.standby_4 = 'UNKNOWN' THEN fix_location + '*' ELSE a.standby_4 END AS suggest_location, " +
                             //"a.standby_4 AS suggest_location, " +
                             "ISNULL(a.order_qty_m, 0) AS order_qty_m, " +
                             "ISNULL(a.order_qty_s, 0) AS order_qty_s, " +
                             "ISNULL(a.receive_qty_m, 0) AS receive_qty_m, " +
                             "ISNULL(a.receive_qty_s, 0) AS receive_qty_s, " +
                             "ISNULL(a.order_qty_m, 0) - ISNULL(a.receive_qty_m, 0) AS w_qty, " +
                             "ISNULL(a.order_qty_s, 0) - ISNULL(a.receive_qty_s, 0) AS w_qty_s, " +
                             "b.sku_name, a.tax_dist, a.pack_code " +
                             "FROM WMS.asn_order_detail a " +
                             "JOIN WMS.mst_sku b ON a.sku = b.sku " +
                             "JOIN WMS.VIEW_mst_sku_barcode c ON a.sku = c.sku " +
                             "WHERE a.inv_org = @InvOrg  AND a.asn_no = @AsnNo " +
                             //"ORDER BY  CASE   WHEN a.standby_4 IS NOT NULL AND a.standby_4 <> '' THEN 1  ELSE 2  END,  a.standby_4;"; // 根据建议库位排序 ,把有数据的显示在前面
                             // "ORDER BY suggest_location"; // 根据建议库位排序 
                                                          //  AND a.consignor = @Consignor
                                                          "ORDER BY a.asn_line_no"; // 根据行号排序


            //string str_sql = "SELECT a.asn_line_no, a.sku,b.barcode,c.suggest_location,ISNULL(a.order_qty_m, 0) AS order_qty_m, ISNULL(a.order_qty_s, 0) AS order_qty_s, ISNULL(a.receive_qty_m, 0) AS receive_qty_m, ISNULL(a.receive_qty_s, 0) AS receive_qty_s, ISNULL(a.order_qty_m, 0) - ISNULL(a.receive_qty_m, 0) AS w_qty, ISNULL(a.order_qty_s, 0) - ISNULL(a.receive_qty_s, 0) AS w_qty_s, b.sku_name, a.tax_dist, a.pack_code FROM WMS.asn_order_detail a JOIN WMS.mst_sku b ON a.sku = b.sku LEFT JOIN WMs.asn_putaway c ON a.asn_no = c.asn_no AND a.sku = c.sku WHERE a.inv_org = @InvOrg AND a.asn_no = @AsnNo  ORDER BY a.asn_line_no";
            try
            {
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    using (SqlCommand command = new SqlCommand(str_sql, connection))
                    {
                        command.Parameters.AddWithValue("@InvOrg", inv);
                        //command.Parameters.AddWithValue("@Consignor", consignor);
                        command.Parameters.AddWithValue("@AsnNo", asnNo);

                        connection.Open();
                        SqlDataReader reader = command.ExecuteReader();

                        List<Dictionary<string, object>> itemsList = new List<Dictionary<string, object>>();

                        if (reader.HasRows)
                        {
                            while (reader.Read())
                            {
                                Dictionary<string, object> itemDetails = new Dictionary<string, object>();

                                for (int i = 0; i < reader.FieldCount; i++)
                                {
                                    itemDetails[reader.GetName(i)] = reader[i];
                                }

                                itemsList.Add(itemDetails);
                            }

                            reader.Close();

                            // Construct result using regular object instantiation
                            Dictionary<string, object> result = new Dictionary<string, object>();
                            result.Add("code", 200);
                            result.Add("data", "查询成功");
                            result.Add("asn_no", asnNo);
                            result.Add("items", itemsList);

                            return new JavaScriptSerializer().Serialize(result);
                        }
                        else
                        {
                            reader.Close();

                            Dictionary<string, object> result = new Dictionary<string, object>();
                            result.Add("code", 202);
                            result.Add("data", "未查询到符合条件的订单");
                            result.Add("asn_no", asnNo);

                            return new JavaScriptSerializer().Serialize(result);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Dictionary<string, object> result = new Dictionary<string, object>();
                result.Add("code", 500);
                result.Add("data", "查询失败: " + ex.Message);
                result.Add("asn_no", asnNo);

                return new JavaScriptSerializer().Serialize(result);
            }
        }

        // 收货商品明细页面
        [WebMethod]
        public string GetBillInformation(string s_sku, string s_asn_no, string s_lineno, string inv, string consignor)
        {
            LogHelpter.AddLog("调用接口【GetBillInformation】");

            string connectionString = WebConfigurationManager.ConnectionStrings["MyDatabaseConnection"].ConnectionString;

            string str_sql = "SELECT a.pack_code, a.sku, b.sku_name, b.lotno, a.tax_dist, b.spec,b.shelflife_flag,b.shelflife_day, c.qty02 as box_spec , c.ti, c.hi, b.main_unit, b.sub_unit, b.shelflife_day, " +
                             "a.order_qty_m, a.receive_qty_m, ISNULL(a.convert_modulus, 1) AS convert_modulus, a.order_price, a.order_unit, " +
                             "(ISNULL(a.cubage, 0) / ISNULL(a.order_qty_m, 1)) AS cubage, (ISNULL(a.gross_weight, 0) / ISNULL(a.order_qty_m, 1)) AS gross_weight, " +
                             "(ISNULL(a.net_weight, 0) / ISNULL(a.order_qty_m, 1)) AS net_weight, a.main_sub_flag, a.asn_line_no, " +
                             " b.fix_flag,  " + //  A位商品区分 (0 非A位商品  1A位商品)
                             " b.fix_location,  " + //  A位库位
                             " b.putaway_rule,  " + //  上架规则
                             "a.lot_attribute1, a.lot_attribute2, a.lot_attribute3, a.lot_attribute4, a.lot_attribute5, a.lot_attribute6, a.lot_attribute7, a.lot_attribute8, " +
                             "a.lot_attribute9, a.lot_attribute10, a.lot_attribute11, a.lot_attribute12, a.lot_attribute13, a.lot_attribute14, a.lot_attribute15 " +
                             "FROM WMS.asn_order_detail a " +
                             "INNER JOIN WMS.mst_sku b ON a.sku = b.sku " +
                             "INNER JOIN WMS.mst_pack c ON a.pack_code = c.pack_code AND a.sku = c.sku " +
                             "WHERE a.inv_org = @InvOrg AND a.consignor = @Consignor AND b.consignor = @Consignor AND c.consignor = @Consignor " +
                             "AND a.sku = @Sku AND a.pack_code = c.pack_code " +
                             "AND a.asn_no = @AsnNo " +
                             "AND a.asn_line_no = @LineNo";

            try
            {
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    using (SqlCommand command = new SqlCommand(str_sql, connection))
                    {
                        // Add SQL parameters to prevent SQL injection
                        command.Parameters.AddWithValue("@InvOrg", inv);
                        command.Parameters.AddWithValue("@Sku", s_sku);
                        command.Parameters.AddWithValue("@AsnNo", s_asn_no);
                        command.Parameters.AddWithValue("@LineNo", s_lineno);
                        command.Parameters.AddWithValue("@Consignor", consignor);

                        connection.Open();
                        SqlDataReader reader = command.ExecuteReader();

                        if (reader.HasRows)
                        {
                            List<Dictionary<string, object>> itemsList = new List<Dictionary<string, object>>();

                            string fix_location = "";
                            string putaway_rule = "";
                            while (reader.Read())
                            {
                                Dictionary<string, object> itemDetails = new Dictionary<string, object>();

                                // 使用逐个添加方式
                                itemDetails["pack_code"] = reader["pack_code"];
                                itemDetails["sku"] = reader["sku"];
                                itemDetails["sku_name"] = reader["sku_name"];
                                itemDetails["lotno"] = reader["lotno"];
                                itemDetails["tax_dist"] = reader["tax_dist"];
                                itemDetails["spec"] = reader["spec"];
                                itemDetails["shelflife_flag"] = reader["shelflife_flag"];
                                itemDetails["shelflife_day"] = reader["shelflife_day"];
                                itemDetails["box_spec"] = reader["box_spec"]; // 箱规 实收数= 箱规 * 箱数（输入的）  by whd add 
                                itemDetails["ti"] = reader["ti"];
                                itemDetails["hi"] = reader["hi"];
                                itemDetails["main_unit"] = reader["main_unit"];
                                itemDetails["sub_unit"] = reader["sub_unit"];
                                itemDetails["shelflife_day"] = reader["shelflife_day"];
                                itemDetails["order_qty_m"] = reader["order_qty_m"];
                                itemDetails["receive_qty_m"] = reader["receive_qty_m"];
                                itemDetails["convert_modulus"] = reader["convert_modulus"];
                                itemDetails["order_price"] = reader["order_price"];
                                itemDetails["order_unit"] = reader["order_unit"];
                                itemDetails["cubage"] = reader["cubage"];
                                itemDetails["gross_weight"] = reader["gross_weight"];
                                itemDetails["net_weight"] = reader["net_weight"];
                                itemDetails["main_sub_flag"] = reader["main_sub_flag"];
                                itemDetails["asn_line_no"] = reader["asn_line_no"];
                                itemDetails["fix_location"] = reader["fix_location"];
                                itemDetails["putaway_rule"] = reader["putaway_rule"];

                                fix_location = itemDetails["fix_location"].ToString().Trim();
                                putaway_rule = itemDetails["putaway_rule"].ToString().Trim();
                                // 如果需要添加其他的 lot_attribute 列，可以在这里添加

                                itemsList.Add(itemDetails);
                            }

                            reader.Close();

                            // 判断  20241110 add 对商品档案中拣货位（A位）及上架规则的判定，如商品无A位或上架规则，这条记录提示用户不能收货，需补齐档案。
                            if (fix_location == null || fix_location == "")
                            {
                                Dictionary<string, object> result2 = new Dictionary<string, object>();
                                result2["code"] = 203;
                                result2["data"] = "商品"+ s_sku+ "没有A位，请补齐档案！"; // "Query successful"
                                result2["asn_no"] = s_asn_no;

                                return new JavaScriptSerializer().Serialize(result2);
                            }

                            if (putaway_rule == null || putaway_rule == "")
                            {
                                Dictionary<string, object> result2 = new Dictionary<string, object>();
                                result2["code"] = 204;
                                result2["data"] = "商品" + s_sku + "没有上架规则，请补齐档案！"; // "Query successful"
                                result2["asn_no"] = s_asn_no;

                                return new JavaScriptSerializer().Serialize(result2);
                            }



                            Dictionary<string, object> result = new Dictionary<string, object>();
                            result["code"] = 200;
                            result["data"] = "查询成功"; // "Query successful"
                            result["asn_no"] = s_asn_no;
                            result["items"] = itemsList;

                            return new JavaScriptSerializer().Serialize(result);
                        }
                        else
                        {
                            Dictionary<string, object> result = new Dictionary<string, object>();
                            result["code"] = 202;
                            result["data"] = "未查询到符合条件的记录"; // "No matching record found"
                            result["asn_no"] = s_asn_no;

                            return new JavaScriptSerializer().Serialize(result);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Dictionary<string, object> result = new Dictionary<string, object>();
                result["code"] = 500;
                result["data"] = "查询失败: " + ex.Message; // "Query failed"
                result["asn_no"] = s_asn_no;

                return new JavaScriptSerializer().Serialize(result);
            }
        }



        //PC上收货确认
        [WebMethod]
        public string Sql_confirm_asnno(string s_org, DataSet ds_asn, string str_userid, string str_rec_no, string json, string str_opt_type)
        {
            LogHelpter.AddLog("调用接口【Sql_confirm_asnno】");
            LogHelpter.AddLog("---收货确认---");
            LogHelpter.AddLog("s_org="+ s_org+ ",str_userid="+ str_userid+ ",str_rec_no="+ str_rec_no+ ",str_opt_type="+ str_opt_type);
            LogHelpter.AddLog("json=" + json );


            // str_opt_type 1: 追加收货明细  2：覆盖收货明细
            string s_str, s_pallet_flag, s_pa_flag, s_sugg_loc, s_asntype, s_taxdist;
            int i_rec_no;
            int i_sy_qty;  // 剩余数量 判断是否超收 （order_qty_m - receive_qty_m - 收货数）
            string s_type, ls_middle, suggest_loc, fix_location, str_supply;
            string str_line, str_line_old;
            string g_usespace;
            SqlCommand command;
            command = new SqlCommand();
            SqlDatabaseController SqldbCenter = new SqlDatabaseController();
            ls_middle = "";
            suggest_loc = "";
            fix_location = "";
            str_supply = "";
            str_line = "";
            str_line_old = "";
            g_usespace = "WMS";
            i_rec_no = 0;
            i_sy_qty = 0;
            string errMsg = "";

            DataSet dataSet = JsonConvert.DeserializeObject<DataSet>(json);
            // 如果JSON字符串不包含DataSet的完整结构（如没有明确的"Table"名称或没有"Tables"集合），则可能需要手动构建DataSet
            if (dataSet.Tables.Count == 0)
            {
                // 手动创建DataTable并添加到DataSet中
                DataTable dataTable = JsonConvert.DeserializeObject<DataTable>(json.Replace("Table", ""));
                dataSet.Tables.Add(dataTable);
            }

            // 现在dataSet包含了解析后的数据

            ds_asn = dataSet;

            try
            {
                SqldbCenter.OpenConnection();
                SqldbCenter.BeginTransaction();
                //得到是否上架计算的参数
                s_str = "select isnull(id_data,'0') from " + g_usespace + ".sys_parameter where inv_org = '" + s_org + "' and id = 8102";
                s_pa_flag = Sql_exec_selectstring(s_str);
                //得到单据类型
                s_str = "select asn_type from " + g_usespace + ".asn_order_header where inv_org = '" + s_org + "' and consignor = '" + ds_asn.Tables[0].Rows[0]["consignor"].ToString().Trim() + "' and asn_no = '" + ds_asn.Tables[0].Rows[0]["asn_no"].ToString().Trim() + "'";
                s_type = Sql_exec_selectstring(s_str);
                s_asntype = s_type;
                //得到供应商编码 20241204 add
                s_str = "select supply from " + g_usespace + ".asn_order_header where inv_org = '" + s_org + "' and consignor = '" + ds_asn.Tables[0].Rows[0]["consignor"].ToString().Trim() + "' and asn_no = '" + ds_asn.Tables[0].Rows[0]["asn_no"].ToString().Trim() + "'";
                str_supply = Sql_exec_selectstring(s_str);
                //循环进行收货确认
                for (int i = 0; i < ds_asn.Tables[0].Rows.Count; i++)
                {
                    if (System.Convert.ToDecimal(ds_asn.Tables[0].Rows[i]["prerec_qty_m"].ToString().Trim()) != 0) //说明有收货数
                    {

                        // add 新增 str_opt_type 1: 追加收货明细  2：覆盖收货明细
                        // 判断收货方式 1: 追加收货明细  2：覆盖收货明细
                        if (str_opt_type == "1")
                        {

                        }

                        if (str_opt_type == "2")
                        {
                            /*string d_consignor = ds_asn.Tables[0].Rows[i]["consignor"].ToString().Trim();
                            string d_asn_no = ds_asn.Tables[0].Rows[i]["asn_no"].ToString().Trim();
                            string d_asn_line_no = ds_asn.Tables[0].Rows[i]["asn_line_no"].ToString().Trim();

                            // 收货明细表
                            s_str = "delete from " + g_usespace + ".asn_receive_detail " +
                                "where inv_org = '" + s_org + "' and consignor = '" + d_consignor + "' " +
                                "and asn_no ='" + d_asn_no + "' and asn_line_no =" + d_asn_line_no;
                            command.CommandText = s_str;
                            SqldbCenter.ExecuteNonQuery(command);

                            // 收货拆板表
                            s_str = "delete from " + g_usespace + ".asn_pallet_split " +
                                "where inv_org = '" + s_org + "' and consignor = '" + d_consignor + "' " +
                                "and asn_no ='" + d_asn_no + "' and asn_line_no =" + d_asn_line_no;
                            command.CommandText = s_str;
                            SqldbCenter.ExecuteNonQuery(command); ;*/
                        }

                        s_str = " select - isnull(order_qty_m,0) + isnull(receive_qty_m,0) + " + ds_asn.Tables[0].Rows[i]["prerec_qty_m"].ToString().Trim() + "  from  " + g_usespace + ".asn_order_detail " +
                        "where inv_org = '" + s_org + "' and consignor = '" + ds_asn.Tables[0].Rows[i]["consignor"].ToString().Trim() + "' and " +
                        " asn_no='" + ds_asn.Tables[0].Rows[i]["asn_no"].ToString().Trim() + "' and asn_line_no =" + ds_asn.Tables[0].Rows[i]["asn_line_no"].ToString().Trim();
                        command.CommandText = s_str;
                        i_sy_qty = SqldbCenter.ExecuteScalar(command);
                        if (i_sy_qty > 0)
                        {
                            errMsg = "当前商品超收数量为" + i_sy_qty +",请重新输入数量";
                            break;
                        }

                        str_line = ds_asn.Tables[0].Rows[i]["asn_line_no"].ToString().Trim();
                        if (str_line != str_line_old)
                        {
                            //得到最大行号
                            s_str = "select isnull(max(receive_line_no),0) from " + g_usespace + ".asn_receive_detail where inv_org = '" + s_org + "' and consignor = '" + ds_asn.Tables[0].Rows[i]["consignor"].ToString().Trim() + "' " +
                                    "and asn_no ='" + ds_asn.Tables[0].Rows[i]["asn_no"].ToString().Trim() + "' and asn_line_no =" + ds_asn.Tables[0].Rows[i]["asn_line_no"].ToString().Trim();
                            command.CommandText = s_str;
                            i_rec_no = SqldbCenter.ExecuteScalar(command);
                            str_line_old = str_line;
                        }
                        i_rec_no = i_rec_no + 1;
                        //判断该商品是托盘管理还是非托盘管理
                        s_str = "select isnull(pallet_flag,'0') pallet_flag from " + g_usespace + ".mst_sku where inv_org = '" + s_org + "' and consignor = '" + ds_asn.Tables[0].Rows[i]["consignor"].ToString().Trim() +
                               "' and sku = '" + ds_asn.Tables[0].Rows[i]["sku"].ToString().Trim() + "'";
                        s_pallet_flag = Sql_exec_selectstring(s_str);
                        s_taxdist = ds_asn.Tables[0].Rows[i]["lot_attribute3"].ToString().Trim();

                        //判断该商品是托盘管理还是非托盘管理
                        s_str = "select isnull(fix_location,'') fix_location from " + g_usespace + ".mst_sku where inv_org = '" + s_org + "' and consignor = '" + ds_asn.Tables[0].Rows[i]["consignor"].ToString().Trim() +
                               "' and sku = '" + ds_asn.Tables[0].Rows[i]["sku"].ToString().Trim() + "'";
                        fix_location = Sql_exec_selectstring(s_str);

                        // 插入收货数据(将一些单据信息往下带)
                        // asn_receive_detail.lot_attribute1 生产日期
                        // asn_receive_detail.shelflife_day  保质期
                        // asn_receive_detail.lot_attribute4  供应商

                        s_str = "insert into " + g_usespace + ".asn_receive_detail(	inv_org,consignor,	asn_no,		asn_line_no,	receive_line_no,       sku,		pack_code,	order_price,	order_unit," +
                                                        "cubage,		gross_weight,	net_weight,	shelflife_day,	receive_qty_m,	receive_qty_s,	receive_present_qty_m,	receive_present_qty_s," +
                                                        "main_sub_flag,	convert_modulus,	receive_day,	fact_status,	lot_attribute1,	lot_attribute2,	lot_attribute3,	lot_attribute4," +
                                                        "lot_attribute5,	lot_attribute6,	lot_attribute7,	lot_attribute8,	lot_attribute9,	lot_attribute10,	lot_attribute11,	lot_attribute12," +
                                                        "lot_attribute13,	lot_attribute14,	lot_attribute15,standby_1,standby_2,standby_3,standby_4,standby_5,standby_6,standby_7,memo,main_dist,made_time,maker,standby_8)" +
                                "values('" + s_org + "','" + ds_asn.Tables[0].Rows[i]["consignor"].ToString().Trim() + "','" + ds_asn.Tables[0].Rows[i]["asn_no"].ToString().Trim() + "'," + ds_asn.Tables[0].Rows[i]["asn_line_no"].ToString().Trim() + "," +
                                System.Convert.ToString(i_rec_no) + ",'" + ds_asn.Tables[0].Rows[i]["sku"].ToString().Trim() + "','" + ds_asn.Tables[0].Rows[i]["pack_code"].ToString().Trim() + "'," + ds_asn.Tables[0].Rows[i]["order_price"].ToString().Trim() + ",'" +
                                ds_asn.Tables[0].Rows[i]["order_unit"].ToString().Trim() + "'," + ds_asn.Tables[0].Rows[i]["prerec_cubage"].ToString().Trim() + "," + ds_asn.Tables[0].Rows[i]["prerec_gross"].ToString().Trim() + "," + ds_asn.Tables[0].Rows[i]["prerec_net"].ToString().Trim() + "," +
                                ds_asn.Tables[0].Rows[i]["shelflife_day"].ToString().Trim() + "," + ds_asn.Tables[0].Rows[i]["prerec_qty_m"].ToString().Trim() + "," + ds_asn.Tables[0].Rows[i]["prerec_qty_s"].ToString().Trim() + "," + ds_asn.Tables[0].Rows[i]["prerec_present_qty_m"].ToString().Trim() + "," +
                                ds_asn.Tables[0].Rows[i]["prerec_present_qty_s"].ToString().Trim() + ",'" + ds_asn.Tables[0].Rows[i]["main_sub_flag"].ToString().Trim() + "'," + ds_asn.Tables[0].Rows[i]["convert_modulus"].ToString().Trim() + ",'" + DateTime.Now.ToString("yyyy.MM.dd") + "',";
                        if (s_pallet_flag == "1")  //非托盘管理，直接插入拆板，并且状态是拆板
                            s_str = s_str + "'1',";
                        else  //托盘，不拆板，状态是收货确认
                            s_str = s_str + "'0',";
                        s_str = s_str + "'" + ds_asn.Tables[0].Rows[i]["lot_attribute1"].ToString().Trim() + "','" + ds_asn.Tables[0].Rows[i]["lot_attribute2"].ToString().Trim() + "','" + ds_asn.Tables[0].Rows[i]["lot_attribute3"].ToString().Trim() + "','" + str_supply.ToString().Trim() + "','" +
                            ds_asn.Tables[0].Rows[i]["lot_attribute5"].ToString().Trim() + "','" + ds_asn.Tables[0].Rows[i]["lot_attribute6"].ToString().Trim() + "','" + ds_asn.Tables[0].Rows[i]["lot_attribute7"].ToString().Trim() + "','" + ds_asn.Tables[0].Rows[i]["lot_attribute8"].ToString().Trim() + "','" +
                            ds_asn.Tables[0].Rows[i]["lot_attribute9"].ToString().Trim() + "','" + ds_asn.Tables[0].Rows[i]["lot_attribute10"].ToString().Trim() + "','" + ds_asn.Tables[0].Rows[i]["lot_attribute11"].ToString().Trim() + "','" + ds_asn.Tables[0].Rows[i]["lot_attribute12"].ToString().Trim() + "','" +
                            ds_asn.Tables[0].Rows[i]["lot_attribute13"].ToString().Trim() + "','" + ds_asn.Tables[0].Rows[i]["lot_attribute14"].ToString().Trim() + "','" + ds_asn.Tables[0].Rows[i]["lot_attribute15"].ToString().Trim() + "','','','','','" + str_rec_no + "',0,0,'" + ds_asn.Tables[0].Rows[i]["memo"].ToString().Trim() + "','A',getdate(),'" + str_userid + "','" + ds_asn.Tables[0].Rows[i]["lot_attribute1"].ToString().Trim() + "')";
                        command.CommandText = s_str;
                        SqldbCenter.ExecuteNonQuery(command);

                        // s_pallet_flag=1,s_pa_flag=2
                        //LogBLL.Err("s_pallet_flag = " + s_pallet_flag + ",s_pa_flag = " + s_pa_flag);


                        //如果是非托盘，插入拆版数据
                        if (s_pallet_flag == "1")
                        {
                            s_str = "insert into " + g_usespace + ".asn_pallet_split(inv_org,consignor,	asn_no,		asn_line_no,	receive_line_no,	pallet_no,	sku,		pack_code,	order_price,order_unit," +
                                                                    "pallet_qty_m,	pallet_qty_s,	main_sub_flag,	convert_modulus,	shelflife_day, cubage,		gross_weight," +
                                                                    "net_weight,	spliter,	split_status,	split_date,	lot_attribute1,	lot_attribute2,	lot_attribute3,	lot_attribute4," +
                                                                    "lot_attribute5,	lot_attribute6,	lot_attribute7,	lot_attribute8,	lot_attribute9,	lot_attribute10,	lot_attribute11,	lot_attribute12," +
                                                                    "lot_attribute13,	lot_attribute14,	lot_attribute15,	standby_1,	standby_2,	standby_3,	standby_4,	standby_5," +
                                                                    "standby_6,	standby_7,	memo,		main_dist,	made_time,	maker)" +
                                    "values('" + s_org + "','" + ds_asn.Tables[0].Rows[i]["consignor"].ToString().Trim() + "','" + ds_asn.Tables[0].Rows[i]["asn_no"].ToString().Trim() + "'," + ds_asn.Tables[0].Rows[i]["asn_line_no"].ToString().Trim() + "," +
                                    System.Convert.ToString(i_rec_no) + ",'000000000000000','" + ds_asn.Tables[0].Rows[i]["sku"].ToString().Trim() + "','" + ds_asn.Tables[0].Rows[i]["pack_code"].ToString().Trim() + "'," + ds_asn.Tables[0].Rows[i]["order_price"].ToString().Trim() + ",'" + ds_asn.Tables[0].Rows[i]["order_unit"].ToString().Trim() + "'," +
                                    System.Convert.ToString(System.Convert.ToDecimal(ds_asn.Tables[0].Rows[i]["prerec_qty_m"].ToString().Trim()) + System.Convert.ToDecimal(ds_asn.Tables[0].Rows[i]["prerec_present_qty_m"].ToString().Trim())) + "," +
                                    System.Convert.ToString(System.Convert.ToDecimal(ds_asn.Tables[0].Rows[i]["prerec_qty_s"].ToString().Trim()) + System.Convert.ToDecimal(ds_asn.Tables[0].Rows[i]["prerec_present_qty_s"].ToString().Trim())) + ",'" +
                                    ds_asn.Tables[0].Rows[i]["main_sub_flag"].ToString().Trim() + "'," + ds_asn.Tables[0].Rows[i]["convert_modulus"].ToString().Trim() + "," + ds_asn.Tables[0].Rows[i]["shelflife_day"].ToString().Trim() + "," + ds_asn.Tables[0].Rows[i]["prerec_cubage"].ToString().Trim() + "," + ds_asn.Tables[0].Rows[i]["prerec_gross"].ToString().Trim() + "," + ds_asn.Tables[0].Rows[i]["prerec_net"].ToString().Trim() + ",'" +
                                    str_userid + "','0','" + DateTime.Now.ToString("yyyy.MM.dd") + "','" + ds_asn.Tables[0].Rows[i]["lot_attribute1"].ToString().Trim() + "','" + ds_asn.Tables[0].Rows[i]["lot_attribute2"].ToString().Trim() + "','" + ds_asn.Tables[0].Rows[i]["lot_attribute3"].ToString().Trim() + "','" + str_supply.ToString().Trim() + "','" +
                                ds_asn.Tables[0].Rows[i]["lot_attribute5"].ToString().Trim() + "','" + ds_asn.Tables[0].Rows[i]["lot_attribute6"].ToString().Trim() + "','" + ds_asn.Tables[0].Rows[i]["lot_attribute7"].ToString().Trim() + "','" + ds_asn.Tables[0].Rows[i]["lot_attribute8"].ToString().Trim() + "','" +
                                ds_asn.Tables[0].Rows[i]["lot_attribute9"].ToString().Trim() + "','" + ds_asn.Tables[0].Rows[i]["lot_attribute10"].ToString().Trim() + "','" + ds_asn.Tables[0].Rows[i]["lot_attribute11"].ToString().Trim() + "','" + ds_asn.Tables[0].Rows[i]["lot_attribute12"].ToString().Trim() + "','" +
                                ds_asn.Tables[0].Rows[i]["lot_attribute13"].ToString().Trim() + "','" + ds_asn.Tables[0].Rows[i]["lot_attribute14"].ToString().Trim() + "','" + ds_asn.Tables[0].Rows[i]["lot_attribute15"].ToString().Trim() + "','','','','','" + str_rec_no + "',0,0,'" + ds_asn.Tables[0].Rows[i]["memo"].ToString().Trim() + "','A',getdate(),'" + str_userid + "')";
                            command.CommandText = s_str;
                            SqldbCenter.ExecuteNonQuery(command);
                        }
                        //如果是非托盘管理并且不需要上架计算,直接插入上架计算表
                        //2012.05.25针对鑫马非托盘管理,收货后是否直接插入上架计算表
                        if (s_pa_flag == "1") //直接插入上架计算表
                        {
                            //得到相应商品的有库存的库位
                            //s_asntype, s_taxdist;location_flag
                            s_str = "select isnull(max(a.location_code),'') from " + g_usespace + ".inv_sku_location a," + g_usespace + ".mst_location b where a.inv_org = '" + s_org + "' and a.consignor = '" + ds_asn.Tables[0].Rows[i]["consignor"].ToString().Trim() + "' and a.sku = '" + ds_asn.Tables[0].Rows[i]["sku"].ToString().Trim() + "' and a.pack_code = '" +
                                    ds_asn.Tables[0].Rows[i]["pack_code"].ToString().Trim() + "' and a.inv_org = b.inv_org and a.location_code = b.location_code and b.lock_flag = '0'";
                            if (s_asntype == "3") //寄售
                            {
                                s_str = s_str + " and b.location_flag = '3'";
                            }
                            else
                            {
                                s_str = s_str + " and b.location_flag = '0'";
                            }
                            if (s_taxdist.Trim() == "保税") //保税区分
                            {
                                s_str = s_str + " and b.standby_flag1 = '1'";
                            }
                            else
                            {
                                s_str = s_str + " and b.standby_flag1 = '0'";
                            }
                            s_sugg_loc = Sql_exec_selectstring(s_str);
                            s_str = "insert into " + g_usespace + ".asn_putaway(inv_org,	consignor,	asn_no,		asn_line_no," +
                                                                               "receive_line_no,	pallet_no,	sku,		pack_code," +
                                                                               "order_price,	order_unit,	putaway_qty_m,	putaway_qty_s," +
                                                                               "main_sub_flag,	convert_modulus,	suggest_location,	shelflife_day," +
                                                                               "cubage,		gross_weight,	net_weight,	putaway_status," +
                                                                               "fact_location,	putawayer,	putaway_flag,	putaway_date," +
                                                                               "print_flag,	printer,	label_flag," +
                                                                               "labeler,	lot_attribute1,	lot_attribute2," +
                                                                               "lot_attribute3,	lot_attribute4,	lot_attribute5,	lot_attribute6," +
                                                                               "lot_attribute7,	lot_attribute8,	lot_attribute9,	lot_attribute10," +
                                                                               "lot_attribute11,	lot_attribute12,	lot_attribute13,	lot_attribute14," +
                                                                               "lot_attribute15,	standby_1,	standby_2,	standby_3," +
                                                                               "standby_4,	standby_5,	standby_6,	standby_7," +
                                                                               "memo,		main_dist,	made_time,	maker) " +
                                    " values('" + s_org + "','" + ds_asn.Tables[0].Rows[i]["consignor"].ToString().Trim() + "','" + ds_asn.Tables[0].Rows[i]["asn_no"].ToString().Trim() + "'," + ds_asn.Tables[0].Rows[i]["asn_line_no"].ToString().Trim() + "," +
                                    System.Convert.ToString(i_rec_no) + ",'000000000000000','" + ds_asn.Tables[0].Rows[i]["sku"].ToString().Trim() + "','" + ds_asn.Tables[0].Rows[i]["pack_code"].ToString().Trim() + "'," + ds_asn.Tables[0].Rows[i]["order_price"].ToString().Trim() + ",'" + ds_asn.Tables[0].Rows[i]["order_unit"].ToString().Trim() + "'," +
                                    System.Convert.ToString(System.Convert.ToDecimal(ds_asn.Tables[0].Rows[i]["prerec_qty_m"].ToString().Trim()) + System.Convert.ToDecimal(ds_asn.Tables[0].Rows[i]["prerec_present_qty_m"].ToString().Trim())) + "," +
                                    System.Convert.ToString(System.Convert.ToDecimal(ds_asn.Tables[0].Rows[i]["prerec_qty_s"].ToString().Trim()) + System.Convert.ToDecimal(ds_asn.Tables[0].Rows[i]["prerec_present_qty_s"].ToString().Trim())) + ",'" +
                                    ds_asn.Tables[0].Rows[i]["main_sub_flag"].ToString().Trim() + "'," + ds_asn.Tables[0].Rows[i]["convert_modulus"].ToString().Trim() + ",'" + s_sugg_loc + "'," + ds_asn.Tables[0].Rows[i]["shelflife_day"].ToString().Trim() + "," + ds_asn.Tables[0].Rows[i]["prerec_cubage"].ToString().Trim() + "," + ds_asn.Tables[0].Rows[i]["prerec_gross"].ToString().Trim() + "," + ds_asn.Tables[0].Rows[i]["prerec_net"].ToString().Trim() + "," +
                                    "'0','','','1','','0','','0','','" + ds_asn.Tables[0].Rows[i]["lot_attribute1"].ToString().Trim() + "','" + ds_asn.Tables[0].Rows[i]["lot_attribute2"].ToString().Trim() + "','" + ds_asn.Tables[0].Rows[i]["lot_attribute3"].ToString().Trim() + "','" + str_supply.ToString().Trim() + "','" +
                                ds_asn.Tables[0].Rows[i]["lot_attribute5"].ToString().Trim() + "','" + ds_asn.Tables[0].Rows[i]["lot_attribute6"].ToString().Trim() + "','" + ds_asn.Tables[0].Rows[i]["lot_attribute7"].ToString().Trim() + "','" + ds_asn.Tables[0].Rows[i]["lot_attribute8"].ToString().Trim() + "','" +
                                ds_asn.Tables[0].Rows[i]["lot_attribute9"].ToString().Trim() + "','" + ds_asn.Tables[0].Rows[i]["lot_attribute10"].ToString().Trim() + "','" + ds_asn.Tables[0].Rows[i]["lot_attribute11"].ToString().Trim() + "','" + ds_asn.Tables[0].Rows[i]["lot_attribute12"].ToString().Trim() + "','" +
                                ds_asn.Tables[0].Rows[i]["lot_attribute13"].ToString().Trim() + "','" + ds_asn.Tables[0].Rows[i]["lot_attribute14"].ToString().Trim() + "','" + ds_asn.Tables[0].Rows[i]["lot_attribute15"].ToString().Trim() + "','','','','','" + str_rec_no + "',0,0,'" + ds_asn.Tables[0].Rows[i]["memo"].ToString().Trim() + "','A',getdate(),'" + str_userid + "')";
                            command.CommandText = s_str;
                            SqldbCenter.ExecuteNonQuery(command);

                            //asn_putaway  lot_attribute1 生产日期
                            //asn_putaway  shelflife_day 保质期
                        }

                        if (s_pa_flag == "2") //调用上架计算存储过程
                        {
                            if (s_pallet_flag == "1") // 只有非托盘的才能调用
                            {
                                command.CommandType = CommandType.StoredProcedure;
                                command.CommandText = g_usespace + ".SP_SQL_PUTAWAY";
                                command.Parameters.Add("@sp_org", SqlDbType.Char, 15);
                                command.Parameters["@sp_org"].Value = s_org;
                                command.Parameters.Add("@sp_csgor", SqlDbType.Char, 15);
                                command.Parameters["@sp_csgor"].Value = ds_asn.Tables[0].Rows[i]["consignor"].ToString().Trim();
                                command.Parameters.Add("@sp_asn", SqlDbType.Char, 20);
                                command.Parameters["@sp_asn"].Value = ds_asn.Tables[0].Rows[i]["asn_no"].ToString().Trim();
                                command.Parameters.Add("@sp_lineno", SqlDbType.Int);
                                command.Parameters["@sp_lineno"].Value = System.Convert.ToInt32(ds_asn.Tables[0].Rows[i]["asn_line_no"].ToString().Trim());
                                command.Parameters.Add("@sp_recno", SqlDbType.Int);
                                command.Parameters["@sp_recno"].Value = i_rec_no;
                                command.Parameters.Add("@sp_pallet", SqlDbType.Char, 15);
                                command.Parameters["@sp_pallet"].Value = "000000000000000";
                                command.Parameters.Add("@sp_qty", SqlDbType.Decimal);
                                command.Parameters["@sp_qty"].Precision = (byte)16;
                                command.Parameters["@sp_qty"].Scale = (byte)4;
                                command.Parameters["@sp_qty"].Value = System.Convert.ToDecimal(ds_asn.Tables[0].Rows[i]["prerec_qty_m"].ToString().Trim());
                                command.Parameters.Add("@sp_sku", SqlDbType.Char, 30);
                                command.Parameters["@sp_sku"].Value = ds_asn.Tables[0].Rows[i]["sku"].ToString().Trim();
                                command.Parameters.Add("@sp_pack", SqlDbType.Char, 30);
                                command.Parameters["@sp_pack"].Value = ds_asn.Tables[0].Rows[i]["pack_code"].ToString().Trim();
                                command.Parameters.Add("@sp_user", SqlDbType.Char, 10);
                                command.Parameters["@sp_user"].Value = str_userid;
                                command.Parameters.Add("@sp_loc", SqlDbType.Char, 20);
                                command.Parameters["@sp_loc"].Direction = ParameterDirection.Output;
                                SqldbCenter.ExecuteNonQuery(command);
                                if (command.Parameters["@sp_loc"].Value.ToString().Trim().Substring(0, 3).ToUpper() == "ERR")
                                {
                                    ls_middle = ls_middle + command.Parameters["@sp_loc"].Value.ToString().Trim();
                                }
                                else
                                {
                                    suggest_loc = suggest_loc + command.Parameters["@sp_loc"].Value.ToString().Trim();
                                }
                                LogHelpter.AddLog("存储过程【SP_SQL_PUTAWAY】 sp_out=" + command.Parameters["@sp_loc"].Value.ToString().Trim());
                                
                                command.Parameters.Clear();
                                command.CommandType = CommandType.Text;
                                if (ls_middle.Trim() != "")
                                {
                                    break;
                                }
                            }
                        }

                        //修改订单明细的状态
                        s_str = "update " + g_usespace + ".asn_order_detail set receive_qty_m = isnull(receive_qty_m,0) + " + ds_asn.Tables[0].Rows[i]["prerec_qty_m"].ToString().Trim() + "," +
                                                                           "receive_qty_s = isnull(receive_qty_s,0) + " + ds_asn.Tables[0].Rows[i]["prerec_qty_s"].ToString().Trim() + "," +
                                                                           "standby_4 = '" + suggest_loc + " '," +
                                                                           
                                                                           //"lot_attribute1 = '" + ds_asn.Tables[0].Rows[i]["lot_attribute1"].ToString().Trim() + " '," + // 生产日期 后改成 记在lot_attribute1里
                                                                           //"lot_attribute14 = '" + ds_asn.Tables[0].Rows[i]["lot_attribute14"].ToString().Trim() + " '," + // 修改价格标志位
                                                                           //"lot_attribute15 = '" + ds_asn.Tables[0].Rows[i]["lot_attribute15"].ToString().Trim() + " '," + // 生产日期 (废弃不用)

                                                                           "receive_present_qty_m = isnull(receive_present_qty_m,0) + " + ds_asn.Tables[0].Rows[i]["prerec_present_qty_m"].ToString().Trim() + "," +
                                                                           "receive_present_qty_s = isnull(receive_present_qty_s,0) + " + ds_asn.Tables[0].Rows[i]["prerec_present_qty_s"].ToString().Trim() + "," +
                                                                           "receive_day = case ltrim(rtrim(isnull(receive_day,''))) when '' then '" + DateTime.Now.ToString("yyyy.MM.dd") + "' else receive_day end," +
                                                                           "receive_status = case when isnull(receive_qty_m,0) +" + ds_asn.Tables[0].Rows[i]["prerec_qty_m"].ToString().Trim() + " >= isnull(order_qty_m,0) then '2' else '1' end," +
                                                                           "main_dist = 'C',update_time = getdate(),updater='" + str_userid + "' where inv_org = '" + s_org + "' and consignor = '" + ds_asn.Tables[0].Rows[i]["consignor"].ToString().Trim() + "' and " +
                                                                           "asn_no='" + ds_asn.Tables[0].Rows[i]["asn_no"].ToString().Trim() + "' and asn_line_no =" + ds_asn.Tables[0].Rows[i]["asn_line_no"].ToString().Trim();
                        command.CommandText = s_str;
                        SqldbCenter.ExecuteNonQuery(command);

                        if (s_type.Trim() == "8" || s_type.Trim() == "9" || s_type.Trim() == "4")  //说明是委外或者外协,调用存储过程
                        {
                            command.CommandType = CommandType.StoredProcedure;
                            command.CommandText = g_usespace + ".SP_SQL_RECEIVE_DELEGATE";
                            command.Parameters.Add("@sp_org", SqlDbType.Char, 15);
                            command.Parameters["@sp_org"].Value = s_org;
                            command.Parameters.Add("@sp_csgor", SqlDbType.Char, 15);
                            command.Parameters["@sp_csgor"].Value = ds_asn.Tables[0].Rows[i]["consignor"].ToString().Trim();
                            command.Parameters.Add("@sp_asn", SqlDbType.Char, 20);
                            command.Parameters["@sp_asn"].Value = ds_asn.Tables[0].Rows[i]["asn_no"].ToString().Trim();
                            command.Parameters.Add("@sp_lineno", SqlDbType.Int);
                            command.Parameters["@sp_lineno"].Value = System.Convert.ToInt32(ds_asn.Tables[0].Rows[i]["asn_line_no"].ToString().Trim());
                            command.Parameters.Add("@sp_sku", SqlDbType.Char, 30);
                            command.Parameters["@sp_sku"].Value = ds_asn.Tables[0].Rows[i]["sku"].ToString().Trim();
                            command.Parameters.Add("@sp_pack", SqlDbType.Char, 30);
                            command.Parameters["@sp_pack"].Value = ds_asn.Tables[0].Rows[i]["pack_code"].ToString().Trim();
                            command.Parameters.Add("@sp_qtym", SqlDbType.Decimal);
                            command.Parameters["@sp_qtym"].Precision = (byte)16;
                            command.Parameters["@sp_qtym"].Scale = (byte)4;
                            command.Parameters["@sp_qtym"].Value = System.Convert.ToDecimal(ds_asn.Tables[0].Rows[i]["prerec_qty_m"].ToString().Trim());
                            command.Parameters.Add("@sp_lot6", SqlDbType.Char, 20);
                            command.Parameters["@sp_lot6"].Value = ds_asn.Tables[0].Rows[i]["lot_attribute6"].ToString().Trim();
                            command.Parameters.Add("@sp_type", SqlDbType.Char, 1);
                            command.Parameters["@sp_type"].Value = s_type;
                            command.Parameters.Add("@sp_user", SqlDbType.Char, 10);
                            command.Parameters["@sp_user"].Value = str_userid;
                            command.Parameters.Add("@sp_out", SqlDbType.VarChar, 100);
                            command.Parameters["@sp_out"].Direction = ParameterDirection.Output;
                            SqldbCenter.ExecuteNonQuery(command);
                            if (command.Parameters["@sp_out"].Value.ToString().Trim() != "1")
                            {
                                ls_middle = command.Parameters["@sp_out"].Value.ToString().Trim();
                            }
                            LogHelpter.AddLog("存储过程【SP_SQL_RECEIVE_DELEGATE】 sp_out=" + command.Parameters["@sp_out"].Value.ToString().Trim());
                            command.Parameters.Clear();
                            command.CommandType = CommandType.Text;
                            if (ls_middle.Trim() != "")
                            {
                                break;
                            }
                        }
                    }
                }
                //全部更新完成后修改表头的状态
                s_str = "select count(*) from " + g_usespace + ".asn_order_detail where inv_org = '" + s_org + "' and consignor = '" + ds_asn.Tables[0].Rows[0]["consignor"].ToString().Trim() + "' and asn_no ='" + ds_asn.Tables[0].Rows[0]["asn_no"].ToString().Trim() + "' and receive_status in ('0','1')";
                command.CommandText = s_str;
                i_rec_no = SqldbCenter.ExecuteScalar(command);
                s_str = "update " + g_usespace + ".asn_order_header set arrive_day = case ltrim(rtrim(isnull(arrive_day,''))) when '' then '" + DateTime.Now.ToString("yyyy.MM.dd") + "' else arrive_day end,";
                if (i_rec_no > 0)//说明还没有收完
                    s_str = s_str + "arrive_status = '1',";
                else
                    s_str = s_str + "arrive_status = '2',";
                s_str = s_str + "main_dist = 'C',update_time=getdate(),updater='" + str_userid + "' where inv_org = '" + s_org + "' and consignor = '" + ds_asn.Tables[0].Rows[0]["consignor"].ToString().Trim() + "' and asn_no ='" + ds_asn.Tables[0].Rows[0]["asn_no"].ToString().Trim() + "'";
                command.CommandText = s_str;
                SqldbCenter.ExecuteNonQuery(command);
                if (ls_middle.Trim() != "")
                {
                    SqldbCenter.Rollback();
                }
                else
                {
                    SqldbCenter.Commit();
                }
            }
            catch (System.Exception)
            {
                SqldbCenter.Rollback();
                throw;
            }
            finally
            {
                SqldbCenter.CloseConnection();
            }

            // add 订单收货模块，收货确认时，如计算不出建议库位（UNKNOWN）时，提示用户拣货位
            // "CASE  WHEN a.suggest_location = 'UNKNOWN' THEN fix_location ELSE a.suggest_location END AS query_location," +     // 查询库位
            if (suggest_loc.Trim() == "UNKNOWN")
            {
                suggest_loc =  fix_location + "*";
            }

            //return suggest_loc.Trim();

            if (errMsg == "")
            {
                var result = new
                {
                    code = 200,
                    data = "收货成功",
                    suggest_loc = suggest_loc,
                };
                return JsonConvert.SerializeObject(result);

            }
            else
            {

                var result = new
                {
                    code = 202,
                    data = "收货超收：" + errMsg,
                };
                return JsonConvert.SerializeObject(result);
            }



        }


        [WebMethod]
        public string HandlePostRequest(string strCheck, string inv)
        {
            string connectionString = WebConfigurationManager.ConnectionStrings["MyDatabaseConnection"].ConnectionString;

            try
            {
                DataSet dsLoc;
                string strSql;

                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    using (SqlCommand command = new SqlCommand())
                    {
                        command.Connection = connection;

                        strSql = "select distinct trim(location_code) location_code from WMS.inv_check_detail where inv_org = @InvOrg and check_no = @Check order by location_code";
                        command.CommandText = strSql;

                        // 添加参数并赋值
                        command.Parameters.AddWithValue("@InvOrg", inv);
                        command.Parameters.AddWithValue("@Check", strCheck);

                        SqlDataAdapter adapter = new SqlDataAdapter(command);
                        dsLoc = new DataSet();
                        adapter.Fill(dsLoc);

                        // JSON化数据集并返回
                        DataTable dt = dsLoc.Tables.Count > 0 ? dsLoc.Tables[0] : new DataTable();

                        // 将 DataTable 转换为 List<Dictionary<string, object>>
                        List<Dictionary<string, object>> items = new List<Dictionary<string, object>>();
                        foreach (DataRow row in dt.Rows)
                        {
                            Dictionary<string, object> item = new Dictionary<string, object>();
                            foreach (DataColumn col in dt.Columns)
                            {
                                item[col.ColumnName] = row[col];
                            }
                            items.Add(item);
                        }

                        Dictionary<string, object> result = new Dictionary<string, object>();
                        result["code"] = 200;
                        result["data"] = "查询成功";
                        result["items"] = items;
                        return new JavaScriptSerializer().Serialize(result);

                    }
                }
            }
            catch (Exception ee)
            {
                // 返回错误信息
                Dictionary<string, object> result = new Dictionary<string, object>();
                result["code"] = 500;
                result["data"] = "获取盘点库位失败!" + ee.ToString();

                return new JavaScriptSerializer().Serialize(result);
            }
        }

        [WebMethod]
        public string GetCheck(string strCheck, string inv, string str_loc, string str_loc1)
        {
            LogHelpter.AddLog("调用接口【GetCheck】");
            string connectionString = WebConfigurationManager.ConnectionStrings["MyDatabaseConnection"].ConnectionString;

            try
            {
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    string sql = @"
                        SELECT 
                            pallet_no, consignor, sku, sku_name, stock_qty_m, stock_qty_s, location_code, ISNULL(check_qty_m,0) check_qty_m, batchno, inv_org 
                        FROM WMS.inv_check_detail 
                        WHERE 
                            inv_org = @Org 
                            AND check_no = @CheckNo 
                            AND location_code >= @LocStart 
                            AND location_code <= @LocEnd 
                            AND check_status = '0' 
                        ORDER BY 
                            consignor, sku, pallet_no ";

                    // 创建并设置参数
                    SqlCommand command = new SqlCommand(sql, connection);
                    command.Parameters.AddWithValue("@Org", inv);
                    command.Parameters.AddWithValue("@CheckNo", strCheck);
                    command.Parameters.AddWithValue("@LocStart", str_loc);
                    command.Parameters.AddWithValue("@LocEnd", str_loc1);

                    // 执行查询
                    SqlDataAdapter adapter = new SqlDataAdapter(command);
                    DataSet dsLoc = new DataSet();
                    adapter.Fill(dsLoc);

                    DataTable dt = dsLoc.Tables.Count > 0 ? dsLoc.Tables[0] : new DataTable();

                    List<Dictionary<string, object>> items = new List<Dictionary<string, object>>();
                    foreach (DataRow row in dt.Rows)
                    {
                        Dictionary<string, object> item = new Dictionary<string, object>();
                        foreach (DataColumn col in dt.Columns)
                        {
                            item[col.ColumnName] = row[col];
                        }
                        items.Add(item);
                    }

                    Dictionary<string, object> result = new Dictionary<string, object>();
                    result["code"] = 200;
                    result["data"] = "查询成功";
                    result["items"] = items;
                    return new JavaScriptSerializer().Serialize(result);
                }
            }
            catch (Exception ee)
            {
                Dictionary<string, object> errorResult = new Dictionary<string, object>();
                errorResult["code"] = 500;
                errorResult["data"] = "获取盘点库位失败!" + ee.ToString();
                return new JavaScriptSerializer().Serialize(errorResult);
            }
        }


        #region 新增3个模块（随行卡录入、发货登记、周转箱回收登记）



        /// <summary>
        /// //////////////新增3个模块（随行卡录入、发货登记、周转箱回收登记）  2024-10-24 add
        /// </summary>
        /// <param name="s_org"></param>
        /// <param name="s_customer_code"></param>
        /// <param name="s_so_day"></param>
        /// <returns></returns>

        // 根据门店和日期，查询出库单
        [WebMethod]
        public string GetSoNoListBySoDay(string s_org, string s_customer_code, string s_so_day)
        {
            string connectionString = WebConfigurationManager.ConnectionStrings["MyDatabaseConnection"].ConnectionString;
            LogBLL.Err("GetSoNoListBySoDay");

            s_so_day = s_so_day.Replace('.', '-');
            string str_sql = " SELECT a.so_no " +
                             " FROM WMS.so_ship_header a " +
                             " WHERE a.inv_org = @InvOrg " +
                             " AND a.customer_code = @CustomerCode " +
                             " AND a.so_day = @SoDay";

            try
            {
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    using (SqlCommand command = new SqlCommand(str_sql, connection))
                    {
                        // Add SQL parameters to prevent SQL injection
                        command.Parameters.AddWithValue("@InvOrg", s_org);
                        command.Parameters.AddWithValue("@CustomerCode", s_customer_code);
                        command.Parameters.AddWithValue("@SoDay", s_so_day);

                        connection.Open();
                        SqlDataReader reader = command.ExecuteReader();

                        if (reader.HasRows)
                        {
                            List<Dictionary<string, object>> itemsList = new List<Dictionary<string, object>>();

                            while (reader.Read())
                            {
                                Dictionary<string, object> itemDetails = new Dictionary<string, object>();

                                // 使用逐个添加方式
                                itemDetails["so_no"] = reader["so_no"];

                                // 如果需要添加其他的 lot_attribute 列，可以在这里添加

                                itemsList.Add(itemDetails);
                            }

                            reader.Close();

                            Dictionary<string, object> result = new Dictionary<string, object>();
                            result["code"] = 200;
                            result["data"] = "查询成功"; // "Query successful"
                            result["customer_code"] = s_customer_code;
                            result["so_day"] = s_so_day;
                            result["items"] = itemsList;

                            return new JavaScriptSerializer().Serialize(result);
                        }
                        else
                        {
                            Dictionary<string, object> result = new Dictionary<string, object>();
                            result["code"] = 202;
                            result["data"] = "未查询到符合条件的记录"; // "No matching record found"
                            result["customer_code"] = s_customer_code;
                            result["so_day"] = s_so_day;

                            return new JavaScriptSerializer().Serialize(result);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Dictionary<string, object> result = new Dictionary<string, object>();
                result["code"] = 500;
                result["data"] = "查询失败: " + ex.Message; // "Query failed"
                result["customer_code"] = s_customer_code;
                result["so_day"] = s_so_day;

                return new JavaScriptSerializer().Serialize(result);
            }
        }


        // 随行卡保存 so_apy_card
        [WebMethod]
        public string Sql_saveSoApyCard(string s_org, string s_consignor, string s_so_no, int i_wholepiece_num, int i_box_num, int i_sellpiece_num, string str_userid)
        {
            string str_sql;
            string str_ls_middle;
            string s_str;
            SqlCommand command;
            command = new SqlCommand();
            SqlDatabaseController SqldbCenter = new SqlDatabaseController();
            str_ls_middle = "";
            string g_usespace = "WMS";
            Dictionary<string, object> result = new Dictionary<string, object>();

            try
            {
                //先修改实际货位
                SqldbCenter.OpenConnection();
                //SqldbCenter.BeginTransaction();
                //然后调用上架确认的存储过程
                //全部更新完成后修改表头的状态


                //s_str = "select count(*) from " + g_usespace + ".asn_order_detail where inv_org = '" + s_org + "' and consignor = '" + ds_asn.Tables[0].Rows[0]["consignor"].ToString().Trim() + "' and asn_no ='" + ds_asn.Tables[0].Rows[0]["asn_no"].ToString().Trim() + "' and receive_status in ('0','1')";
                //command.CommandText = s_str;
                //i_rec_no = SqldbCenter.ExecuteScalar(command);


                //s_str = "update " + g_usespace + ".asn_order_header set arrive_day = case ltrim(rtrim(isnull(arrive_day,''))) when '' then '" + DateTime.Now.ToString("yyyy.MM.dd") + "' else arrive_day end,";
                //s_str = s_str + "main_dist = 'C',update_time=getdate(),updater='" + str_userid + "' where inv_org = '" + s_org + "' and consignor = '" + ds_asn.Tables[0].Rows[0]["consignor"].ToString().Trim() + "' and asn_no ='" + ds_asn.Tables[0].Rows[0]["asn_no"].ToString().Trim() + "'";


                //插入收货数据
                s_str = " insert into " + g_usespace + ".so_apy_card(" +
                        " inv_org,consignor,	so_no, wholepiece_num, box_num, sellpiece_num, " +
                        " main_dist,made_time,maker ) " +
                        " values('" + s_org + "','" + s_consignor + "','" + s_so_no + "'," + i_wholepiece_num.ToString().Trim() + "," + i_box_num.ToString().Trim() + "," + i_sellpiece_num.ToString().Trim() + "," +
                        " 'A',getdate(),'" + str_userid + "')";
                LogBLL.Err(s_str);

                command.CommandText = s_str;
                SqldbCenter.ExecuteNonQuery(command);

                result["code"] = 200;
                result["data"] = "保存成功";


                //if (str_ls_middle.Trim() != "")
                //{
                //    SqldbCenter.Rollback();
                //}
                //else
                //{
                //    SqldbCenter.Commit();
                //}

            }
            catch (System.Exception ex)
            {

                result["code"] = 500;
                result["data"] = "保存失败: " + ex.Message;

            }
            finally
            {
                SqldbCenter.CloseConnection();
            }

            return new JavaScriptSerializer().Serialize(result);
        }

        // 发货登记 so_drivers
        [WebMethod]
        public string Sql_saveSoDrivers(string s_org, string s_consignor, string s_so_no, string s_drivers, string s_userid)
        {
            string str_sql;
            string str_ls_middle;
            string s_str;
            SqlCommand command;
            command = new SqlCommand();
            SqlDatabaseController SqldbCenter = new SqlDatabaseController();
            str_ls_middle = "";
            string g_usespace = "WMS";
            Dictionary<string, object> result = new Dictionary<string, object>();

            try
            {

                SqldbCenter.OpenConnection();
                //SqldbCenter.BeginTransaction();

                //插入收货数据
                s_str = " insert into " + g_usespace + ".so_drivers (" +
                        " inv_org,consignor,	so_no, drivers, " +
                        " main_dist,made_time,maker ) " +
                        " values('" + s_org + "','" + s_consignor + "','" + s_so_no + "'," + s_drivers.ToString().Trim() + "," +
                        " 'A',getdate(),'" + s_userid + "')";
                LogBLL.Err(s_str);

                command.CommandText = s_str;
                SqldbCenter.ExecuteNonQuery(command);


                result["code"] = 200;
                result["data"] = "保存成功";



            }
            catch (System.Exception ex)
            {

                result["code"] = 500;
                result["data"] = "保存失败: " + ex.Message;

            }
            finally
            {
                SqldbCenter.CloseConnection();
            }

            return new JavaScriptSerializer().Serialize(result);
        }

        // 周转箱回收登记 so_rec_boxs
        [WebMethod]
        public string Sql_saveSoRecBoxs(string s_org, string s_consignor, string s_so_no, int i_recboxs_num, string s_userid)
        {
            string str_sql;
            string str_ls_middle;
            string s_str;
            SqlCommand command;
            command = new SqlCommand();
            SqlDatabaseController SqldbCenter = new SqlDatabaseController();
            str_ls_middle = "";
            string g_usespace = "WMS";
            Dictionary<string, object> result = new Dictionary<string, object>();

            try
            {

                SqldbCenter.OpenConnection();
                //SqldbCenter.BeginTransaction();

                //插入收货数据
                s_str = " insert into " + g_usespace + ".so_rec_boxs (" +
                        " inv_org,consignor,	so_no, recboxs_num, " +
                        " main_dist,made_time,maker ) " +
                        " values('" + s_org + "','" + s_consignor + "','" + s_so_no + "'," + i_recboxs_num.ToString().Trim() + "," +
                        " 'A',getdate(),'" + s_userid + "')";

                LogBLL.Err(s_str);

                command.CommandText = s_str;
                SqldbCenter.ExecuteNonQuery(command);


                result["code"] = 200;
                result["data"] = "保存成功";



            }
            catch (System.Exception ex)
            {

                result["code"] = 500;
                result["data"] = "保存失败: " + ex.Message;

            }
            finally
            {
                SqldbCenter.CloseConnection();
            }

            return new JavaScriptSerializer().Serialize(result);
        }


        #endregion

        ////////////////////////新增3个模块（随行卡录入、发货登记、周转箱回收登记）  2024-10-24 add//////////////////////////

        #region 手持-手持-门店退货确认

        /**
         * 获取门店号
         *
         * @param s_inv 组织机构
         * @return 结果
         */
        [WebMethod]
        public string SQL_REJ_getStoreCd(string s_inv)
        {
            LogHelpter.AddLog("调用接口【SQL_REJ_getStoreCd】");
            string connectionString = WebConfigurationManager.ConnectionStrings["MyDatabaseConnection"].ConnectionString;

            /*string str_sql = " SELECT a.so_no " +
                             " FROM WMS.so_ship_header a " +
                             " WHERE a.inv_org = @InvOrg " +
                             " AND a.customer_code = @CustomerCode " +
                             " AND a.so_day = @SoDay";*/

            // SELECT '026' as storeCd, '一团火连锁金苑小区店' as storeName   ",
            /*string str_sql = " select a.consignor  as storeCd ,a.consignor_name as storeName " +
                " from WMS.mst_customer a where a.customer_flag = 1 and a.consignor in ('026','027','028') ";*/

            string str_sql = @"select 
distinct a.supply as storeCd,
a.supply + '-' + b.consignor_name as storeName 
from WMS.asn_order_header a 
left join WMS.mst_customer b on a.supply = b.consignor and b.customer_flag = 1
where 1=1 
 and a.standby_4 = 1 
                                and a.asn_type = '4' and a.arrive_status <>'2'"; 

            /*string str_sql = String.Format(@" 
                                    select a.supply as storeCd  ,c.consignor_name as storeName
                                    from WMS.asn_order_header a 
                                    LEFT JOIN WMS.mst_customer c ON a.inv_org = c.inv_org   AND LTRIM(RTRIM(a.supply)) = LTRIM(RTRIM(c.consignor)) AND c.customer_flag = 1
                                    where 1=1 
                                    AND a.inv_org = {0} 
                                    and a.standby_4 = 1 and a.asn_type = '4' ",
                                s_inv);
*/
            try
            {

                DataTable dt = OpDB.GetDataTable(connectionString, str_sql);
                if (dt != null && dt.Rows.Count > 0)
                {


                    var result = new
                    {
                        code = 200,
                        data = "查询成功",
                        s_inv = s_inv,
                        items = dt
                    };
                    return JsonConvert.SerializeObject(result);



                }
                else
                {
                    var result = new
                    {
                        code = 202,
                        data = "未查询到符合条件的记录",
                        s_inv = s_inv
                    };

                    return JsonConvert.SerializeObject(result);
                }



            }
            catch (Exception ex)
            {
                var result = new
                {
                    code = 500,
                    data = "查询失败: " + ex.Message, // "Query failed"
                    s_inv = s_inv
                };
                return JsonConvert.SerializeObject(result);
            }
        }

        /**
         * 获取数据字典-拒收原因
         *
         * @param s_inv 组织机构
         * @return 结果
         */
        [WebMethod]
        public string SQL_REJ_getDistReg(string s_inv)
        {

            string str_sql = String.Format(@" 
                                SELECT dictionary_code as dict_code, content1 as dict_value
                                FROM wms.sys_dictionary 
                                where 1=1 
                                AND use_flag = '1' 
                                AND dictionary_name = 'rej' ",
                                s_inv);

            try
            {

                DataTable dt = OpDB.GetDataTable(connectionString, str_sql);
                if (dt != null && dt.Rows.Count > 0)
                {


                    var result = new
                    {
                        code = 200,
                        data = "查询成功",
                        s_inv = s_inv,
                        items = dt
                    };
                    return JsonConvert.SerializeObject(result);



                }
                else
                {
                    var result = new
                    {
                        code = 202,
                        data = "未查询到符合条件的记录",
                        s_inv = s_inv
                    };

                    return JsonConvert.SerializeObject(result);
                }



            }
            catch (Exception ex)
            {
                var result = new
                {
                    code = 500,
                    data = "查询失败: " + ex.Message, // "Query failed"
                    s_inv = s_inv
                };
                return JsonConvert.SerializeObject(result);
            }
        }

        /**
         * 获取数据字典-待处理取
         *
         * @param s_inv 组织机构
         * @return 结果
         */
        [WebMethod]
        public string SQL_REJ_getDistWh(string s_inv)
        {

            string str_sql = String.Format(@" 
                                SELECT dictionary_code as dict_code, content1 as dict_value
                                FROM wms.sys_dictionary 
                                where 1=1 
                                AND use_flag = '1' 
                                AND dictionary_name = 'wh' ",
                                s_inv);

            try
            {

                DataTable dt = OpDB.GetDataTable(connectionString, str_sql);
                if (dt != null && dt.Rows.Count > 0)
                {


                    var result = new
                    {
                        code = 200,
                        data = "查询成功",
                        s_inv = s_inv,
                        items = dt
                    };
                    return JsonConvert.SerializeObject(result);



                }
                else
                {
                    var result = new
                    {
                        code = 202,
                        data = "未查询到符合条件的记录",
                        s_inv = s_inv
                    };

                    return JsonConvert.SerializeObject(result);
                }



            }
            catch (Exception ex)
            {
                var result = new
                {
                    code = 500,
                    data = "查询失败: " + ex.Message, // "Query failed"
                    s_inv = s_inv
                };
                return JsonConvert.SerializeObject(result);
            }
        }

        /**
         * 退货确认-显示列表
         *
         * @param s_inv 组织机构
         * @param s_store_code 店号
         * @return 结果
         */
        [WebMethod]
        public string SQL_REJ_getStoreToDCList(string s_inv, string s_store_code)
        {

            LogBLL.Err("s_inv=" + s_inv + ",s_store_code=" + s_store_code);

            // 表头数据（不用了，改成查询明细列表）
            /*string str_sql = String.Format(@" 
select  a.asn_no, a.consignor, a.order_day, a.supply,
case a.arrive_status when '0' then '未到' when '1' then '部分' when '2' then '全到'  else '' end arrive_status_name,
b.sku_th_count,c.sku_ys_count
from WMS.asn_order_header a 
left join (select inv_org,consignor,asn_no,count(sku) sku_th_count from WMS.asn_order_detail GROUP BY inv_org,consignor,asn_no) b on a.inv_org = b.inv_org and a.consignor = b.consignor and a.asn_no = b.asn_no 
left join (select inv_org,consignor,asn_no,count(sku) sku_ys_count from WMS.asn_order_detail GROUP BY inv_org,consignor,asn_no) c on a.inv_org = c.inv_org and a.consignor = c.consignor and a.asn_no = c.asn_no 
where 1=1 
                                
                                AND a.inv_org = '{0}' and a.standby_4 = 1 
                                and a.asn_type = '4' and a.supply = '{1}'   ",
                                s_inv, s_store_code);  // 是否需要加索引优化*/

            string str_sql = String.Format(@" 
select  a.asn_no, a.consignor, a.order_day, a.supply,
case a.arrive_status when '0' then '未到' when '1' then '部分' when '2' then '全到'  else '' end arrive_status_name,
isnull(b.sku_th_count,0) sku_th_count ,isnull( c.sku_ys_count,0) sku_ys_count
from WMS.asn_order_header a 
left join (select inv_org,consignor,asn_no,count(sku) sku_th_count from WMS.asn_order_detail GROUP BY inv_org,consignor,asn_no) b on a.inv_org = b.inv_org and a.consignor = b.consignor and a.asn_no = b.asn_no 
left join (select inv_org,consignor,asn_no,count(sku) sku_ys_count from WMS.asn_rej_detail GROUP BY inv_org,consignor,asn_no) c on a.inv_org = c.inv_org and a.consignor = c.consignor and a.asn_no = c.asn_no 
where 1=1 
                                
                                AND a.inv_org = '{0}' and a.standby_4 = 1 
                                and a.asn_status = '0'
                                and a.asn_type = '4' and a.supply = '{1}'   ",
                                s_inv, s_store_code);  // 是否需要加索引优化



            try
            {

                DataTable dt = OpDB.GetDataTable(connectionString, str_sql);
                if (dt != null && dt.Rows.Count > 0)
                {


                    var result = new
                    {
                        code = 200,
                        data = "查询成功",
                        s_inv = s_inv,
                        s_store_code,
                        items = dt
                    };
                    return JsonConvert.SerializeObject(result);



                }
                else
                {
                    var result = new
                    {
                        code = 202,
                        data = "未查询到符合条件的记录",
                        s_inv = s_inv,
                        s_store_code = s_store_code
                    };

                    return JsonConvert.SerializeObject(result);
                }



            }
            catch (Exception ex)
            {
                var result = new
                {
                    code = 500,
                    data = "查询失败: " + ex.Message, // "Query failed"
                    s_inv = s_inv,
                    s_store_code
                };
                return JsonConvert.SerializeObject(result);
            }
        }

        /**
         * 退货确认-获取门店退单商品详情
         *
         * @param s_inv 组织机构
         * @param s_store_code 店号
         * @param s_barcode 条码
         * @return 结果
         */
        [WebMethod]
        public string SQL_REJ_getStoreToDCDetailByBarcode(string s_inv, string s_store_code, string s_barcode)
        {
            LogHelpter.AddLog("调用接口【SQL_REJ_getStoreToDCDetailByBarcode】");
            //123
            string str_sql = String.Format(@" 
SELECT a.inv_org,a.consignor,a.asn_no,a.asn_line_no,c.order_day,
ISNULL(a.order_qty_m, 0) AS order_qty_m,
ISNULL(a.order_qty_s, 0) AS order_qty_s,
ISNULL(f.rec_qty_m, 0) AS receive_qty_m,
ISNULL(f.rec_qty_s, 0) AS receive_qty_s,
ISNULL(a.order_qty_m, 0) - ISNULL(a.receive_qty_m, 0) AS w_qty, 
ISNULL(a.order_qty_s, 0) - ISNULL(a.receive_qty_s, 0) AS w_qty_s, 
a.sku,b.sku_name,b.sku,e.barcode,d.qty02 as box_spec, a.tax_dist, a.pack_code ,a.order_price,a.order_unit,b.shelflife_day,c.supply,a.reasonCode,a.reasonName
FROM WMS.asn_order_detail a
LEFT JOIN WMS.mst_sku b ON a.sku = b.sku 
INNER JOIN WMS.asn_order_header c ON a.inv_org = c.inv_org and a.consignor = c.consignor  and a.asn_no = c.asn_no 
LEFT JOIN WMS.mst_pack d ON a.pack_code = d.pack_code AND a.sku = d.sku      
INNER JOIN WMS.mst_sku_barcode e ON a.sku = e.sku      
LEFT JOIN WMS.asn_rej_detail f ON a.inv_org = f.inv_org and a.consignor = f.consignor  and a.asn_no = f.asn_no and a.asn_line_no = f.asn_line_no 
WHERE 1=1 
                                and a.inv_org = '{0}'  and c.standby_4 = 1 
                                and c.asn_type = '4' and c.supply = '{1}' AND e.barcode = '{2}'   ",
                                s_inv, s_store_code, s_barcode); // 是否要加索引优化

            /*            string str_sql = "SELECT a.asn_line_no, a.sku,b.barcode,a.standby_4 AS suggest_location, " +
                                         "ISNULL(a.order_qty_m, 0) AS order_qty_m, " +
                                         "ISNULL(a.order_qty_s, 0) AS order_qty_s, " +
                                         "ISNULL(a.receive_qty_m, 0) AS receive_qty_m, " +
                                         "ISNULL(a.receive_qty_s, 0) AS receive_qty_s, " +
                                         "ISNULL(a.order_qty_m, 0) - ISNULL(a.receive_qty_m, 0) AS w_qty, " +
                                         "ISNULL(a.order_qty_s, 0) - ISNULL(a.receive_qty_s, 0) AS w_qty_s, " +
                                         "b.sku_name, a.tax_dist, a.pack_code " +
                                         "FROM WMS.asn_order_detail a " +
                                         "JOIN WMS.mst_sku b ON a.sku = b.sku " +
                                         "WHERE a.inv_org = @InvOrg AND a.asn_no = @AsnNo " +
                                         "ORDER BY suggest_location"; // 根据建议库位排序*/

            try
            {

                DataTable dt = OpDB.GetDataTable(connectionString, str_sql);
                if (dt != null && dt.Rows.Count > 0)
                {


                    var result = new
                    {
                        code = 200,
                        data = "查询成功",
                        s_inv,
                        s_store_code,
                        s_barcode,
                        items = dt
                    };
                    return JsonConvert.SerializeObject(result);



                }
                else
                {
                    var result = new
                    {
                        code = 202,
                        data = "没有退库记录",
                        // data = "未查询到符合条件的记录",
                        s_inv,
                        s_store_code,
                        s_barcode,
                    };

                    return JsonConvert.SerializeObject(result);
                }



            }
            catch (Exception ex)
            {
                var result = new
                {
                    code = 500,
                    data = "查询失败: " + ex.Message, // "Query failed"
                    s_inv,
                    s_store_code,
                    s_barcode
                };
                return JsonConvert.SerializeObject(result);
            }
        }

        /**
         * 退货确认-保存门店退单明细收货数据
         *
         * @param s_inv 组织机构
         * @param s_userid 操作人
         * @param json json数据
         * @return 结果
         */
        [WebMethod]
        public string SQL_REJ_saveStoreToDCDetail(string s_inv, string s_userid, string json)
        {
            LogHelpter.AddLog("调用接口【SQL_REJ_saveStoreToDCDetail】");
            //s_inv = "01.01.002.01";
            //s_userid = "11";
            //json = "{\"ds_asn\":[{\"inv_org\":\"01.01.002.01\",\"consignor\":\"DC001          \",\"asn_no\":\"RRD20241031567026   \",\"asn_line_no\":\"2\",\"sku\":\"0100064                       \",\"pack_code\":\"0100064                       \",\"order_unit\":\"          \",\"shelflife_day\":\"0\",\"order_qty_m\":\"5.0\",\"order_qty_s\":\"5.0\",\"receive_day\":\"2024.10.31\",\"rec_qty_m\":\"5\",\"rec_qty_s\":\"5\",\"rec_location\":\"04910001                                                                        \",\"rec_memo\":\"退库待处理区        \",\"rej_qty_m\":\"0\",\"rej_qty_s\":\"0\",\"rej_memo\":\"未收到实物          \"}]}\r\ntime:2024/11/6 21:03:48";


            LogHelpter.AddLog("【SQL_REJ_saveStoreToDCDetail】参数json=" + json);
            LogBLL.Err("【SQL_REJ_saveStoreToDCDetail】参数json=" + json);

            /*string str_sql = String.Format(@" 
update wms.asn_rej_detail set pack_code='{0}',order_unit='{1}',
                                and c.asn_type = '4' and c.supply = {1} AND b.barcode = {2}   ",
                                s_inv, s_store_code, s_barcode);*/

            SqlCommand command;
            command = new SqlCommand();
            SqlDatabaseController SqldbCenter = new SqlDatabaseController();

            DataSet ds_asn = null;
            DataSet dataSet = JsonConvert.DeserializeObject<DataSet>(json);
            // 如果JSON字符串不包含DataSet的完整结构（如没有明确的"Table"名称或没有"Tables"集合），则可能需要手动构建DataSet
            if (dataSet.Tables.Count == 0)
            {
                // 手动创建DataTable并添加到DataSet中
                DataTable dataTable = JsonConvert.DeserializeObject<DataTable>(json.Replace("Table", ""));
                dataSet.Tables.Add(dataTable);
            }
            ds_asn = dataSet;
            List<string> sqlList = new List<string>();

            var result = new { code = 202, data = "保存失败：插入数据未成功", };


            try
            {

                string str_sql = "";

                //先修改实际货位
                SqldbCenter.OpenConnection();
                SqldbCenter.BeginTransaction();
                //然后调用上架确认的存储过程

                str_sql = "select count(*) from wms.asn_rej_detail where  inv_org = '" + s_inv + "' and consignor='" + ds_asn.Tables[0].Rows[0]["consignor"].ToString().Trim() + "' and asn_no='" + ds_asn.Tables[0].Rows[0]["asn_no"].ToString().Trim() + "' and asn_line_no='" + ds_asn.Tables[0].Rows[0]["asn_line_no"].ToString().Trim() + "'  ";

                LogBLL.Err("str_sql=" + str_sql);

                int cnt = Convert.ToInt32(OpDB.GetObject(connectionString, str_sql));

                LogBLL.Err("str_sql=" + str_sql);
                LogBLL.Err("cnt=" + cnt);

                if (cnt == 0)
                {
                    str_sql = "insert into wms.asn_rej_detail(	inv_org,consignor,	asn_no,		asn_line_no,	 " +
                                                            "sku,pack_code,	order_unit," +
                                                            "shelflife_day,	order_qty_m,	order_qty_s," +
                                                            "receive_day,	rec_qty_m,	rec_qty_s, rec_location," +
                                                            "rec_memo,	rej_qty_m,	rej_qty_s,rej_code, rej_memo," +
                                                            "memo,main_dist,made_time,maker)" +
                                    "values('" + s_inv + "','" + ds_asn.Tables[0].Rows[0]["consignor"].ToString().Trim() + "','" + ds_asn.Tables[0].Rows[0]["asn_no"].ToString().Trim() + "'," + ds_asn.Tables[0].Rows[0]["asn_line_no"].ToString().Trim() + "," +
                                    "'" + ds_asn.Tables[0].Rows[0]["sku"].ToString().Trim() + "','" + ds_asn.Tables[0].Rows[0]["pack_code"].ToString().Trim() + "','" + ds_asn.Tables[0].Rows[0]["order_unit"].ToString().Trim() + "'," +
                                    "'" + ds_asn.Tables[0].Rows[0]["shelflife_day"].ToString().Trim() + "','" + ds_asn.Tables[0].Rows[0]["order_qty_m"].ToString().Trim() + "','" + ds_asn.Tables[0].Rows[0]["order_qty_s"].ToString().Trim() + "'," +
                                    "'" + ds_asn.Tables[0].Rows[0]["receive_day"].ToString().Trim() + "','" + ds_asn.Tables[0].Rows[0]["rec_qty_m"].ToString().Trim() + "','" + ds_asn.Tables[0].Rows[0]["rec_qty_s"].ToString().Trim() + "','" + ds_asn.Tables[0].Rows[0]["rec_location"].ToString().Trim() + "'," +
                                    "'" + ds_asn.Tables[0].Rows[0]["rec_memo"].ToString().Trim() + "','" + ds_asn.Tables[0].Rows[0]["rej_qty_m"].ToString().Trim() + "','" + ds_asn.Tables[0].Rows[0]["rej_qty_s"].ToString().Trim() + "','" + ds_asn.Tables[0].Rows[0]["rej_code"].ToString().Trim() + "','" + ds_asn.Tables[0].Rows[0]["rej_memo"].ToString().Trim() + "'," +
                                    "'','A',getdate(),'" + s_userid + "')";
                    
                }
                else
                {
                    str_sql = "update wms.asn_rej_detail set " +
                        "rec_qty_m = '" + ds_asn.Tables[0].Rows[0]["rec_qty_m"].ToString().Trim() + "', " +
                        "rec_qty_s = '" + ds_asn.Tables[0].Rows[0]["rec_qty_s"].ToString().Trim() + "', " +
                        "rej_qty_m = '" + ds_asn.Tables[0].Rows[0]["rej_qty_m"].ToString().Trim() + "', " +
                        "rej_qty_s = '" + ds_asn.Tables[0].Rows[0]["rej_qty_s"].ToString().Trim() + "', " +
                        "update_time = getdate(), " +
                        "updater = '" + s_userid + "'" +

                        " where inv_org ='" + s_inv + "' and consignor ='" + ds_asn.Tables[0].Rows[0]["consignor"].ToString().Trim() + "' and asn_no ='" + ds_asn.Tables[0].Rows[0]["asn_no"].ToString().Trim() + "'" +
                        " and asn_line_no ='" + ds_asn.Tables[0].Rows[0]["asn_line_no"].ToString().Trim() + "'";

                }


                LogBLL.Err(str_sql);

                sqlList.Add(str_sql);
                if (OpDB.SaveDataToDB(connectionString, sqlList))
                {

                    result = new { code = 200, data = "保存成功", };
                    
                }
                else
                {
                    result = new { code = 202, data = "保存失败：插入数据未成功", };
                    
                }



            }
            catch (Exception ex)
            {
                result = new { code = 500, data = "保存失败: " + ex.Message, };
            }
            return JsonConvert.SerializeObject(result);

        }

        /**
         * 退货确认-门店退单提交(调用存储过程)
         *
         * @param s_inv 组织机构
         * @param s_userid 操作人
         * @param json json数据
         * @return 结果
         */
        [WebMethod]
        public string SQL_REJ_confirm(string s_inv, string s_userid, string json)
        {
            LogHelpter.AddLog("调用接口【SQL_REJ_confirm】");
            LogHelpter.AddLog("【SQL_REJ_exec_proc_rf_rej】参数json=" + json);
            LogBLL.Err("【SQL_REJ_exec_proc_rf_rej】参数json=" + json);

            var result = new
            {
                code = 202,
                data = "参数不正确", // "Query failed"
                sp_out = ""

            };

            DataSet ds_data = null;
            DataSet dataSet = JsonConvert.DeserializeObject<DataSet>(json);
            // 如果JSON字符串不包含DataSet的完整结构（如没有明确的"Table"名称或没有"Tables"集合），则可能需要手动构建DataSet
            if (dataSet.Tables.Count == 0)
            {
                // 手动创建DataTable并添加到DataSet中
                DataTable dataTable = JsonConvert.DeserializeObject<DataTable>(json.Replace("Table", ""));
                dataSet.Tables.Add(dataTable);
            }
            ds_data = dataSet;


            SqlCommand command;
            command = new SqlCommand("WMS.SP_SQL_RF_REJ"); // 手持退库入库确认
            SqlDatabaseController SqldbCenter = new SqlDatabaseController();
            try
            {
                SqldbCenter.OpenConnection();
                SqldbCenter.BeginTransaction();

                string errMsg = "";
                string str_ls_middle = "";
                for (int i = 0; i < ds_data.Tables.Count; i++)
                {
                    string inv_org, consignor, asn_no;
                    inv_org = ds_data.Tables[0].Rows[i]["inv_org"].ToString().Trim();
                    consignor = ds_data.Tables[0].Rows[i]["consignor"].ToString().Trim();
                    asn_no = ds_data.Tables[0].Rows[i]["asn_no"].ToString().Trim();

                    command.CommandType = CommandType.StoredProcedure;
                    command.Parameters.Add("@sp_org", SqlDbType.Char, 15);
                    command.Parameters["@sp_org"].Value = s_inv;
                    command.Parameters.Add("@sp_csgor", SqlDbType.Char, 15);
                    command.Parameters["@sp_csgor"].Value = consignor;
                    command.Parameters.Add("@sp_asn", SqlDbType.Char, 20);
                    command.Parameters["@sp_asn"].Value = asn_no;
                    command.Parameters.Add("@sp_user", SqlDbType.Char, 10);
                    command.Parameters["@sp_user"].Value = s_userid;
                    command.Parameters.Add("@sp_out", SqlDbType.VarChar, 50);
                    command.Parameters["@sp_out"].Direction = ParameterDirection.Output;
                    SqldbCenter.ExecuteNonQuery(command);


                    LogBLL.Err("sp_out=" + command.Parameters["@sp_out"].Value.ToString().Trim());
                    /*string sp_out = command.Parameters["@sp_out"].Value.ToString().Trim();
                    if (sp_out != "0")
                    {
                        errMsg += "单据" + asn_no + "处理失败;";
                    }*/

                    if (command.Parameters["@sp_out"].Value.ToString().Trim() != "0")
                    {
                        str_ls_middle = str_ls_middle + command.Parameters["@sp_out"].Value.ToString().Trim();
                    }
                    command.Parameters.Clear();

                }

                


                if (str_ls_middle.Trim() == "")
                {
                    SqldbCenter.Commit();

                    result = new
                    {
                        code = 200,
                        data = "操作成功 ",
                        sp_out = str_ls_middle
                    };
                }
                else
                {
                    SqldbCenter.Rollback();

                    result = new
                    {
                        code = 202,
                        data = "操作失败： " + str_ls_middle,
                        sp_out = str_ls_middle
                    };
                }





            }
            catch (System.Exception ex)
            {
                //throw;
                SqldbCenter.Rollback();
                result = new
                {
                    code = 500,
                    data = "操作失败: " + ex.Message, // "Query failed"
                    sp_out = ""

                };
            }
            finally
            {
                SqldbCenter.CloseConnection();
            }
            //return command.Parameters["@sp_out"].Value.ToString().Trim();
            return JsonConvert.SerializeObject(result);
        }

        // 根据单号查询对应的商品信息
        [WebMethod]
        public string SQL_REJ_DisplayDetail(string asnNo, string inv)
        {
            LogHelpter.AddLog("调用接口【SQL_REJ_DisplayDetail】 asnNo=" + asnNo);

            string connectionString = WebConfigurationManager.ConnectionStrings["MyDatabaseConnection"].ConnectionString;
            DataSet ds_temp = new DataSet();
            // CASE  WHEN a.suggest_location = 'UNKNOWN' THEN fix_location ELSE a.suggest_location END AS query_location,
            string str_sql = "SELECT a.asn_line_no, a.sku,b.barcode,c.barcode_list," +
                             "b.fix_location, " +
                             "CASE  WHEN a.standby_4 = 'UNKNOWN' THEN fix_location + '*' ELSE a.standby_4 END AS suggest_location, " +
                //"a.standby_4 AS suggest_location, " +
                             "ISNULL(a.order_qty_m, 0) AS order_qty_m, " +
                             "ISNULL(a.order_qty_s, 0) AS order_qty_s, " +
                             "ISNULL(a.receive_qty_m, 0) AS receive_qty_m, " +
                             "ISNULL(a.receive_qty_s, 0) AS receive_qty_s, " +
                             "ISNULL(a.order_qty_m, 0) - ISNULL(a.receive_qty_m, 0) AS w_qty, " +
                             "ISNULL(a.order_qty_s, 0) - ISNULL(a.receive_qty_s, 0) AS w_qty_s, " +
                             "d.standby_4,d.asn_status,d.asn_type, " +
                             "b.sku_name, a.tax_dist, a.pack_code " +
                             "FROM WMS.asn_order_detail a " +
                             "INNER JOIN WMS.asn_order_header d ON a.inv_org = d.inv_org and a.consignor = d.consignor  and a.asn_no = d.asn_no " +
                             "JOIN WMS.mst_sku b ON a.sku = b.sku " +
                             "JOIN WMS.VIEW_mst_sku_barcode c ON a.sku = c.sku " +
                             "WHERE 1=1 and a.inv_org = @InvOrg  AND a.asn_no = @AsnNo " +
                             "  and d.standby_4 = 1 "+
                             "  and d.asn_status = '0' "+
                             "  and d.asn_type = '4' " +
                //"ORDER BY  CASE   WHEN a.standby_4 IS NOT NULL AND a.standby_4 <> '' THEN 1  ELSE 2  END,  a.standby_4;"; // 根据建议库位排序 ,把有数据的显示在前面
                // "ORDER BY suggest_location"; // 根据建议库位排序 
                //  AND a.consignor = @Consignor
                                                          "ORDER BY a.asn_line_no"; // 根据行号排序


            //string str_sql = "SELECT a.asn_line_no, a.sku,b.barcode,c.suggest_location,ISNULL(a.order_qty_m, 0) AS order_qty_m, ISNULL(a.order_qty_s, 0) AS order_qty_s, ISNULL(a.receive_qty_m, 0) AS receive_qty_m, ISNULL(a.receive_qty_s, 0) AS receive_qty_s, ISNULL(a.order_qty_m, 0) - ISNULL(a.receive_qty_m, 0) AS w_qty, ISNULL(a.order_qty_s, 0) - ISNULL(a.receive_qty_s, 0) AS w_qty_s, b.sku_name, a.tax_dist, a.pack_code FROM WMS.asn_order_detail a JOIN WMS.mst_sku b ON a.sku = b.sku LEFT JOIN WMs.asn_putaway c ON a.asn_no = c.asn_no AND a.sku = c.sku WHERE a.inv_org = @InvOrg AND a.asn_no = @AsnNo  ORDER BY a.asn_line_no";
            try
            {
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    using (SqlCommand command = new SqlCommand(str_sql, connection))
                    {
                        command.Parameters.AddWithValue("@InvOrg", inv);
                        //command.Parameters.AddWithValue("@Consignor", consignor);
                        command.Parameters.AddWithValue("@AsnNo", asnNo);

                        connection.Open();
                        SqlDataReader reader = command.ExecuteReader();

                        List<Dictionary<string, object>> itemsList = new List<Dictionary<string, object>>();

                        if (reader.HasRows)
                        {
                            while (reader.Read())
                            {
                                Dictionary<string, object> itemDetails = new Dictionary<string, object>();

                                for (int i = 0; i < reader.FieldCount; i++)
                                {
                                    itemDetails[reader.GetName(i)] = reader[i];
                                }

                                itemsList.Add(itemDetails);
                            }

                            reader.Close();

                            // Construct result using regular object instantiation
                            Dictionary<string, object> result = new Dictionary<string, object>();
                            result.Add("code", 200);
                            result.Add("data", "查询成功");
                            result.Add("asn_no", asnNo);
                            result.Add("items", itemsList);

                            return new JavaScriptSerializer().Serialize(result);
                        }
                        else
                        {
                            reader.Close();

                            Dictionary<string, object> result = new Dictionary<string, object>();
                            result.Add("code", 202);
                            result.Add("data", "未查询到符合条件的订单");
                            result.Add("asn_no", asnNo);

                            return new JavaScriptSerializer().Serialize(result);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Dictionary<string, object> result = new Dictionary<string, object>();
                result.Add("code", 500);
                result.Add("data", "查询失败: " + ex.Message);
                result.Add("asn_no", asnNo);

                return new JavaScriptSerializer().Serialize(result);
            }
        }


        #endregion

        /******************************************************** 
        * ------------------------------------------------------
        * ------手持-补货操作管理  2024-10-31 add
        * ------------------------------------------------------
        ********************************************************/

        #region 手持-补货管理-移库



        /**
         * 获取下拉框数据-移库作业库区
         *
         * @param s_inv 组织机构
         * @return 结果
         */
        [WebMethod]
        public string SQL_MOVE_getDistArea(string s_inv)
        {

            string str_sql = String.Format(@" 
                                SELECT area_code, area_name
                                FROM wms.mst_area 
                                where 1=1 
                                AND inv_org = '{0}'   ",
                                s_inv);

            try
            {

                DataTable dt = OpDB.GetDataTable(connectionString, str_sql);
                if (dt != null && dt.Rows.Count > 0)
                {


                    var result = new
                    {
                        code = 200,
                        data = "查询成功",
                        s_inv = s_inv,
                        items = dt
                    };
                    return JsonConvert.SerializeObject(result);



                }
                else
                {
                    var result = new
                    {
                        code = 202,
                        data = "未查询到符合条件的记录",
                        s_inv = s_inv
                    };

                    return JsonConvert.SerializeObject(result);
                }



            }
            catch (Exception ex)
            {
                var result = new
                {
                    code = 500,
                    data = "查询失败: " + ex.Message, // "Query failed"
                    s_inv = s_inv
                };
                return JsonConvert.SerializeObject(result);
            }
        }

        /**
         * 获取下拉框数据-移库作业类型
         *
         * @param s_inv 组织机构
         * @return 结果
         */
        [WebMethod]
        public string SQL_MOVE_getDistMoveReason(string s_inv)
        {

            string str_sql = String.Format(@" 
                                SELECT '00' AS move_reason_code, '正常' AS move_reason
                                UNION ALL
                                SELECT '02', '自动补货'
                                UNION ALL
                                SELECT '03', '强制补货'  ",
                                s_inv);

            try
            {

                DataTable dt = OpDB.GetDataTable(connectionString, str_sql);
                if (dt != null && dt.Rows.Count > 0)
                {


                    var result = new
                    {
                        code = 200,
                        data = "查询成功",
                        s_inv = s_inv,
                        items = dt
                    };
                    return JsonConvert.SerializeObject(result);



                }
                else
                {
                    var result = new
                    {
                        code = 202,
                        data = "未查询到符合条件的记录",
                        s_inv = s_inv
                    };

                    return JsonConvert.SerializeObject(result);
                }



            }
            catch (Exception ex)
            {
                var result = new
                {
                    code = 500,
                    data = "查询失败: " + ex.Message, // "Query failed"
                    s_inv = s_inv
                };
                return JsonConvert.SerializeObject(result);
            }
        }

        /**
         * 获取移库列表
         *
         * @param s_inv 组织机构
         * @param s_area_code 区域编号
         * @param s_move_reason 移库原因代码
         * @return 结果
         */
        [WebMethod]
        public string SQL_MOVE_getMoveList(string s_inv, string s_area_code, string s_move_reason)
        {
            
            LogHelpter.AddLog(String.Format("调用接口【SQL_MOVE_getMoveList】 参数:inv={0},s_area_code={1},s_move_reason={2}", s_inv, s_area_code, s_move_reason));


            string str_sql = String.Format(@" 
                                select a.inv_org,a.consignor,a.move_no ,a.move_line_no,a.moveout_location,a.moveout_pallet,a.movein_location,a.movein_pallet,
                                a.from_warehouse,a.to_warehouse,a.move_qty_m,
                                case b.move_reason when '00' then '正常' when '02' then '自动补货' when '03' then '强制补货'  else '' end move_reason_name 
                                from wms.inv_move_detail a
                                INNER JOIN wms.inv_move_header b on a.move_no = b.move_no and b.move_type='2'
                                where a.inv_org = '{0}' AND a.move_status = '0' and LEFT(a.moveout_location, 4) = '{1}' and b.move_reason = '{2}'  ",
                                s_inv, s_area_code, s_move_reason);

            try
            {

                DataTable dt = OpDB.GetDataTable(connectionString, str_sql);
                if (dt != null && dt.Rows.Count > 0)
                {


                    var result = new
                    {
                        code = 200,
                        data = "查询成功",
                        s_inv = s_inv,
                        s_area_code,
                        s_move_reason,
                        items = dt
                    };
                    return JsonConvert.SerializeObject(result);



                }
                else
                {
                    var result = new
                    {
                        code = 202,
                        data = "未查询到符合条件的记录",
                        s_inv = s_inv,
                        s_area_code,
                        s_move_reason,
                    };

                    return JsonConvert.SerializeObject(result);
                }



            }
            catch (Exception ex)
            {
                var result = new
                {
                    code = 500,
                    data = "查询失败: " + ex.Message, // "Query failed"
                    s_inv = s_inv,
                    s_area_code,
                    s_move_reason,
                };
                return JsonConvert.SerializeObject(result);
            }
        }

        /**
         * 获取明细列表
         *
         * @param s_inv 组织机构
         * @param s_area_code 区域编号
         * @param s_move_reason 移库原因代码
         * @return 结果
         */
        [WebMethod]
        public string SQL_MOVE_getMoveDetail(string s_inv, string s_area_code, string s_move_reason)
        {
            
            LogHelpter.AddLog(String.Format("调用接口【SQL_MOVE_getMoveDetail】 参数:inv={0},s_area_code={1},s_move_reason={2}", s_inv, s_area_code, s_move_reason));

            string str_sql = String.Format(@" 
                                select a.inv_org,a.consignor,a.move_no ,a.move_line_no,a.moveout_location,a.moveout_pallet,a.batchno,a.movein_location,a.movein_pallet,
                                a.stock_qty_m,c.qty02 as box_spec,
                                d.sku,d.sku_name,d.barcode,e.barcode_list,d.spec,
                                a.from_warehouse,a.to_warehouse,a.move_qty_m,
                                case b.move_reason when '00' then '正常' when '02' then '自动补货' when '03' then '强制补货'  else '' end move_reason_name
                                from wms.inv_move_detail a
                                INNER JOIN wms.inv_move_header b on a.inv_org = b.inv_org and a.consignor = b.consignor and  a.move_no = b.move_no and b.move_type='2'
                                INNER JOIN WMS.mst_pack c ON a.pack_code = c.pack_code AND a.sku = c.sku                                
                                LEFT JOIN wms.mst_sku d on a.sku = d.sku
                                LEFT JOIN wms.VIEW_mst_sku_barcode e on a.sku = e.sku
                                
                                where a.inv_org = '{0}' AND a.move_status = '0' and LEFT(a.moveout_location, 4) = '{1}' and b.move_reason = '{2}'  ",
                                s_inv, s_area_code, s_move_reason);

            try
            {

                DataTable dt = OpDB.GetDataTable(connectionString, str_sql);
                if (dt != null && dt.Rows.Count > 0)
                {


                    var result = new
                    {
                        code = 200,
                        data = "查询成功",
                        s_inv = s_inv,
                        s_area_code,
                        s_move_reason,
                        items = dt
                    };
                    return JsonConvert.SerializeObject(result);



                }
                else
                {
                    var result = new
                    {
                        code = 202,
                        data = "未查询到符合条件的记录",
                        s_inv = s_inv,
                        s_area_code,
                        s_move_reason,
                    };

                    return JsonConvert.SerializeObject(result);
                }



            }
            catch (Exception ex)
            {
                var result = new
                {
                    code = 500,
                    data = "查询失败: " + ex.Message, // "Query failed"
                    s_inv = s_inv,
                    s_area_code,
                    s_move_reason,
                };
                return JsonConvert.SerializeObject(result);
            }
        }

        /**
         * 补货管理-移库确认(调用存储过程)
         *
         * @param s_inv 组织机构
         * @param s_consignor 货主
         * @param s_moveno 移库单号
         * @param s_move_line_no 移库单行号
         * @param s_moveout_location 移出库位
         * @param s_moveout_pallet 移出托盘编号
         * @param s_batchno 内部批号
         * @param i_tmp_qty_m 数量
         * @param s_userid 操作人
         * @return 结果
         */
        [WebMethod]
        public string SQL_MOVE_confirm(string s_org, string s_consignor, string s_move_no, string s_move_line_no, string s_moveout_location,
            string s_moveout_pallet, string s_batchno, int i_tmp_qty_m, string s_userid)
        {
            LogHelpter.AddLog("调用接口【SQL_MOVE_confirm】 s_move_no=" + s_move_no + ",s_move_line_no=" + s_move_line_no + ",s_moveout_location=" + s_moveout_location + ",s_moveout_pallet=" + s_moveout_pallet + ",s_batchno=" + s_batchno + ",i_tmp_qty_m=" + i_tmp_qty_m.ToString());
            //"s_org":"01.01.002.01","s_consignor":"DC001","s_move_no":"MV00000035","s_userid":"123","s_move_line_no":"1","s_moveout_location":"01913062",
            //"s_moveout_pallet":"000000000000000","s_batchno":"6953029710242","i_tmp_qty_m":500

            /*s_org = "01.01.002.01";
            s_consignor = "DC001";
            s_move_no = "MV00000035";
            s_userid = "123";
            s_move_line_no = "1";
            s_moveout_location = "01913062";
            s_moveout_pallet = "000000000000000";
            s_batchno = "6953029710242";
            i_tmp_qty_m = 500;*/

            SqlCommand command;
            command = new SqlCommand();
            SqlDatabaseController SqldbCenter = new SqlDatabaseController();
            string str_ls_middle = "";
            var result = new
            {
                code = 500,
                data = "失败",
                sp_out = "",
            };


            try
            {

                //先修改数量
                SqldbCenter.OpenConnection();
                SqldbCenter.BeginTransaction();
                //然后调用存储过程

                string str_sql = "update wms.inv_move_detail set " +
                    "tmp_qty_m = '" + i_tmp_qty_m + "', " +
                    "update_time = getdate(), " +
                    "updater = '" + s_userid + "'" +

                    " where inv_org ='" + s_org + "' and consignor ='" + s_consignor + "' and move_no ='" + s_move_no + "'"+ 
                    " and move_line_no ='" + s_move_line_no + "' and moveout_location ='" + s_moveout_location + "'" +
                    " and moveout_pallet ='" + s_moveout_pallet + "' and batchno ='" + s_batchno + "'";

                LogBLL.Err("str_sql=" + str_sql);

                command.CommandType = CommandType.Text;
                command.CommandText = str_sql;
                SqldbCenter.ExecuteNonQuery(command);

                command.CommandText = "WMS.SP_SQL_MOVE_CONFIRM";
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add("@sp_org", SqlDbType.Char, 15);
                command.Parameters["@sp_org"].Value = s_org;
                command.Parameters.Add("@sp_consignor", SqlDbType.Char, 15);
                command.Parameters["@sp_consignor"].Value = s_consignor;
                command.Parameters.Add("@sp_moveno", SqlDbType.Char, 20);
                command.Parameters["@sp_moveno"].Value = s_move_no;
                command.Parameters.Add("@sp_user", SqlDbType.Char, 10);
                command.Parameters["@sp_user"].Value = s_userid;
                command.Parameters.Add("@sp_out", SqlDbType.VarChar, 50);
                command.Parameters["@sp_out"].Direction = ParameterDirection.Output;
                SqldbCenter.ExecuteNonQuery(command);
                if (command.Parameters["@sp_out"].Value.ToString().Trim() != "0")
                {
                    str_ls_middle = str_ls_middle + command.Parameters["@sp_out"].Value.ToString().Trim();
                }
                command.Parameters.Clear();


                if (str_ls_middle.Trim() == "")
                {
                    SqldbCenter.Commit();

                    result = new
                    {
                        code = 200,
                        data = "操作成功 ",
                        sp_out = str_ls_middle
                    };
                }
                else
                {
                    SqldbCenter.Rollback();

                    result = new
                    {
                        code = 202,
                        data = "操作失败： "+ str_ls_middle,
                        sp_out = str_ls_middle
                    };
                }



            }
            catch (System.Exception ex)
            {
                SqldbCenter.Rollback();
                result = new
                {
                    code = 500,
                    data = "查询失败: " + ex.Message, // "Query failed"
                    sp_out = ""

                };
            }
            finally
            {
                SqldbCenter.CloseConnection();
            }
            //return command.Parameters["@sp_out"].Value.ToString().Trim();

            return JsonConvert.SerializeObject(result);
        }

        #endregion

        /******************************************************** 
        * ------------------------------------------------------
        * ------手持-退货拣货管理  2024-10-31 add
        * ------------------------------------------------------
        ********************************************************/

        #region 手持-退货拣货管理


        /**
         * 供应商退货-显示供应商
         *
         * @param s_inv 组织机构
         * @return 结果
         */
        [WebMethod]
        public string SQL_DTS_getDistSupply(string s_inv)
        {

            // 01.01.002.01   DC001
            // and a.standby_8 =  '2'  // 全部发货
            LogHelpter.AddLog("调用接口【SQL_DTS_getDistSupply】");

            string view = String.Format(@"
select a.inv_org,a.customer_code,a.customer_name,a.so_no
from wms.so_ship_header a,wms.so_ship_detail b 
where 1=1
and a.inv_org = b.inv_org and a.consignor = b.consignor 
and a.so_no = b.so_no
and a.inv_org = '{0}'
and a.so_type = '1'
and a.so_flag = '0'
and a.so_status <> '4'
and a.standby_8 <> '2' 
group by a.inv_org,a.customer_code,a.customer_name,a.so_no
", s_inv);

            string str_sql = String.Format(@" 
select distinct customer_code,customer_name  from ({0}) a order by a.customer_code,a.customer_name
                                   ",
                                view);



            try
            {

                DataTable dt = OpDB.GetDataTable(connectionString, str_sql);
                if (dt != null && dt.Rows.Count > 0)
                {


                    var result = new
                    {
                        code = 200,
                        data = "查询成功",
                        items = dt
                    };
                    return JsonConvert.SerializeObject(result);



                }
                else
                {
                    var result = new
                    {
                        code = 202,
                        data = "未查询到符合条件的记录",
                    };

                    return JsonConvert.SerializeObject(result);
                }



            }
            catch (Exception ex)
            {
                var result = new
                {
                    code = 500,
                    data = "查询失败: " + ex.Message,
                };
                return JsonConvert.SerializeObject(result);
            }
        }

        /**
         * 供应商退货-显示退单号
         *
         * @param s_inv 组织机构
         * @return 结果
         */
        [WebMethod]
        public string SQL_DTS_getDistSoNo(string s_inv)
        {
            LogHelpter.AddLog("调用接口【SQL_DTS_getDistSoNo】");
            // 01.01.002.01   DC001


            string view = String.Format(@"
select a.inv_org,a.customer_code,a.customer_name,a.so_no
from wms.so_ship_header a,wms.so_ship_detail b 
where 1=1
and a.inv_org = b.inv_org and a.consignor = b.consignor 
and a.so_no = b.so_no
and a.inv_org = '{0}'
and a.so_type = '1'
and a.so_flag = '0'
and a.so_status <> '4'
and a.standby_8 <> '2' 
group by a.inv_org,a.customer_code,a.customer_name,a.so_no
", s_inv);

            string str_sql = String.Format(@" 
select *  from ({0}) a  order by a.inv_org,a.customer_code,a.customer_name,a.so_no
                                   ",
                                view);



            try
            {

                DataTable dt = OpDB.GetDataTable(connectionString, str_sql);
                if (dt != null && dt.Rows.Count > 0)
                {


                    var result = new
                    {
                        code = 200,
                        data = "查询成功",
                        items = dt
                    };
                    return JsonConvert.SerializeObject(result);



                }
                else
                {
                    var result = new
                    {
                        code = 202,
                        data = "未查询到符合条件的记录",
                    };

                    return JsonConvert.SerializeObject(result);
                }



            }
            catch (Exception ex)
            {
                var result = new
                {
                    code = 500,
                    data = "查询失败: " + ex.Message,
                };
                return JsonConvert.SerializeObject(result);
            }
        }


        /**
         * 供应商退货-显示列表
         *
         * @param s_inv 组织机构
         * @param s_customer_code 供应商编号
         * @param s_so_no 退单单号
         * @return 结果
         */
        [WebMethod]
        public string SQL_DTS_getDCToSupplyList(string s_inv, string s_customer_code, string s_so_no)
        {
            LogHelpter.AddLog("调用接口【SQL_DTS_getDCToSupplyList】");
            // 01.01.002.01   DC001
            string strQuery = "";

            if (s_customer_code != "" && s_customer_code != null)
            {
                strQuery += "and a.customer_code = '" + s_customer_code + "'";
            }

            if (s_so_no != "" && s_so_no != null)
            {
                strQuery += "and a.so_no = '" + s_so_no + "'";
            }

            string str_sql = String.Format(@"
select a.inv_org,a.consignor,a.customer_code,a.customer_name,a.so_no,count(b.so_line_no)
from wms.so_ship_header a,wms.so_ship_detail b 
where 1=1
and a.inv_org = b.inv_org and a.consignor = b.consignor 
and a.so_no = b.so_no
and a.inv_org = '{0}'
{1}
and a.so_type = '1'
and a.so_flag = '0'
and a.so_status in ('0','1')
and a.standby_8 <> '2' 
group by a.inv_org,a.consignor,a.customer_code,a.customer_name,a.so_no
order by a.inv_org,a.consignor,a.customer_code,a.customer_name,a.so_no
", s_inv, strQuery);





            try
            {

                DataTable dt = OpDB.GetDataTable(connectionString, str_sql);
                if (dt != null && dt.Rows.Count > 0)
                {


                    var result = new
                    {
                        code = 200,
                        data = "查询成功",
                        items = dt
                    };
                    return JsonConvert.SerializeObject(result);



                }
                else
                {
                    var result = new
                    {
                        code = 202,
                        data = "未查询到符合条件的记录",
                    };

                    return JsonConvert.SerializeObject(result);
                }



            }
            catch (Exception ex)
            {
                var result = new
                {
                    code = 500,
                    data = "查询失败: " + ex.Message,
                };
                return JsonConvert.SerializeObject(result);
            }
        }


        /**
         * 退货确认-做退货单拣货分配计算(调用存储过程)
         *
         * @param s_inv 组织机构
         * @param s_consignor 操作人
         * @param s_so_no 退单号
         * @param s_userid 操作人
         * @return 结果
         */
        [WebMethod]
        public string SQL_DTS_billPickTH(string s_inv, string s_consignor, string s_so_no, string s_userid)
        {
            LogHelpter.AddLog("调用接口【SQL_DTS_billPickTH】");
            var result = new
            {
                code = 202,
                data = "参数不正确", // "Query failed"
                sp_out = ""

            };



            SqlCommand command;
            command = new SqlCommand("WMS.SP_SQL_BILL_PICK_TH"); // 手持退库入库确认
            SqlDatabaseController SqldbCenter = new SqlDatabaseController();
            try
            {
                SqldbCenter.OpenConnection();

                string errMsg = "";

                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add("@sp_org", SqlDbType.Char, 15);
                command.Parameters["@sp_org"].Value = s_inv;
                command.Parameters.Add("@sp_csgor", SqlDbType.Char, 15);
                command.Parameters["@sp_csgor"].Value = s_consignor;
                command.Parameters.Add("@sp_so", SqlDbType.Char, 20);
                command.Parameters["@sp_so"].Value = s_so_no;
                command.Parameters.Add("@sp_wave", SqlDbType.Char, 20);
                command.Parameters["@sp_wave"].Value = "0"; // 0-一单拣货
                command.Parameters.Add("@sp_preparer", SqlDbType.Char, 10);
                command.Parameters["@sp_preparer"].Value = s_userid;
                command.Parameters.Add("@sp_user", SqlDbType.Char, 10);
                command.Parameters["@sp_user"].Value = s_userid;
                SqldbCenter.ExecuteNonQuery(command);


                result = new
                {
                    code = 200,
                    data = "操作成功", // "Query failed"
                    sp_out = ""

                };




            }
            catch (System.Exception ex)
            {
                //throw;
                result = new
                {
                    code = 500,
                    data = "操作失败: " + ex.Message, // "Query failed"
                    sp_out = ""

                };
            }
            finally
            {
                SqldbCenter.CloseConnection();
            }
            //return command.Parameters["@sp_out"].Value.ToString().Trim();
            return JsonConvert.SerializeObject(result);
        }


        /**
         * 供应商退货-显示商品明细清单列表
         *
         * @param s_inv 组织机构
         * @param s_customer_code 供应商编号
         * @param s_so_no 退单单号
         * @return 结果
         */
        [WebMethod]
        public string SQL_DTS_getBillPickDetailList(string s_inv, string s_consignor, string s_so_no)
        {
            LogHelpter.AddLog("调用接口【SQL_DTS_getBillPickDetailList】");
            // 01.01.002.01   DC001

            string str_sql = String.Format(@"
select a.inv_org,a.consignor,a.so_no,a.so_line_no,a.batchno,a.location_code,a.pallet_no,
a.sku,b.sku_name,b.barcode,e.barcode_list,d.qty02 as box_spec,
a.standby_3 退货原因,
c.plan_so_qty_m 请退数量,
a.plan_pick_qty_m - a.pick_qty_m 可退库存,
a.tmp_pick_qty_m 实退数录入
from wms.so_pick_detail a
INNER JOIN wms.mst_sku b on a.sku = b.sku
INNER JOIN wms.so_ship_detail c on a.inv_org = c.inv_org and a.consignor = c.consignor and a.so_no = c.so_no and a.sku = c.SKU
LEFT JOIN wms.mst_pack d on a.sku = d.sku 
LEFT JOIN wms.VIEW_mst_sku_barcode e on a.sku = e.sku 
where 1=1
and a.inv_org = '{0}' and a.consignor='{1}' 
and a.so_no = '{2}'
and a.pcik_status in ('0','1')
", s_inv, s_consignor, s_so_no);





            try
            {

                DataTable dt = OpDB.GetDataTable(connectionString, str_sql);
                if (dt != null && dt.Rows.Count > 0)
                {


                    var result = new
                    {
                        code = 200,
                        data = "查询成功",
                        s_inv,
                        s_consignor,
                        s_so_no,
                        items = dt
                    };
                    return JsonConvert.SerializeObject(result);



                }
                else
                {
                    var result = new
                    {
                        code = 202,
                        data = "未查询到符合条件的记录",
                        s_inv,
                        s_consignor,
                        s_so_no,
                    };

                    return JsonConvert.SerializeObject(result);
                }



            }
            catch (Exception ex)
            {
                var result = new
                {
                    code = 500,
                    data = "查询失败: " + ex.Message,
                    s_inv,
                    s_consignor,
                    s_so_no,
                };
                return JsonConvert.SerializeObject(result);
            }
        }

        /// <summary>
        /// 补货管理-保存商品明细数据（更新数量 tmp_pick_qty_m）
        /// </summary>
        /// <param name="s_org">组织机构</param>
        /// <param name="s_consignor"></param>
        /// <param name="s_so_no"></param>
        /// <param name="s_so_line_no"></param>
        /// <param name="s_batchno"></param>
        /// <param name="s_location_code"></param>
        /// <param name="s_pallet_no"></param>
        /// <param name="s_userid">操作人</param>
        /// <param name="i_tmp_pick_qty_m"></param>
        /// <returns>结果</returns>
        [WebMethod]
        public string SQL_DTS_saveBillPickCDetail(string s_org, string s_consignor, string s_so_no, string s_so_line_no, string s_batchno,
            string s_location_code, string s_pallet_no, string s_userid, int i_tmp_pick_qty_m)
        {

            LogHelpter.AddLog("调用接口【SQL_DTS_saveBillPickCDetail】s_userid=" + s_userid);

            List<string> sqlList = new List<string>();


            string str_sql = "update wms.so_pick_detail set " +
                "tmp_pick_qty_m = '" + i_tmp_pick_qty_m + "', " +
                "update_time = getdate(), " +
                "updater = '" + s_userid + "'" +

                " where inv_org ='" + s_org + "' and consignor ='" + s_consignor + "' and so_no ='" + s_so_no + "'" +
                " and so_line_no ='" + s_so_line_no + "' and batchno ='" + s_batchno + "'" +
                " and location_code ='" + s_location_code + "' and pallet_no ='" + s_pallet_no +  "'";

            LogBLL.Err("str_sql=" + str_sql);

            try
            {
                sqlList.Add(str_sql);
                if(OpDB.SaveDataToDB(connectionString, sqlList))
                {

                    var result = new { code = 200, data = "保存成功", };
                    return JsonConvert.SerializeObject(result);
                }
                else
                {
                    var result = new { code = 202, data = "保存失败：更新数据未成功", };
                    return JsonConvert.SerializeObject(result);
                }

                //DataTable dt = OpDB.GetDataTable(connectionString, str_sql);




            }
            catch (Exception ex)
            {
                var result = new { code = 500, data = "保存失败: " + ex.Message, };
                return JsonConvert.SerializeObject(result);
            }
        }


        /// <summary>
        /// 补货管理-商品明细清单提交确认(调用存储过程)
        /// </summary>
        /// <param name="s_org">组织机构</param>
        /// <param name="s_consignor">货主</param>
        /// <param name="s_so_no">移库单号</param>
        /// <param name="s_userid">操作人</param>
        /// <returns>结果</returns>
        [WebMethod]
        public string SQL_DTS_billPick_confirm(string s_org, string s_consignor, string s_so_no, string s_userid)
        {
            LogHelpter.AddLog("调用接口【SQL_DTS_billPick_confirm】 s_so_no=" + s_so_no + " ,s_userid=" + s_userid);

            SqlCommand command;
            command = new SqlCommand("WMS.SP_SQL_PICK_LIST");
            SqlDatabaseController SqldbCenter = new SqlDatabaseController();
            string ls_middle = "";

            var result = new
            {
                code = 500,
                data = "失败",
                sp_out = "",
            };


            try
            {
                SqldbCenter.OpenConnection();
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add("@sp_org", SqlDbType.Char, 15);
                command.Parameters["@sp_org"].Value = s_org;
                command.Parameters.Add("@sp_csgor", SqlDbType.Char, 15);
                command.Parameters["@sp_csgor"].Value = s_consignor;
                command.Parameters.Add("@sp_so", SqlDbType.Char, 20);
                command.Parameters["@sp_so"].Value = s_so_no;
                command.Parameters.Add("@sp_user", SqlDbType.Char, 10);
                command.Parameters["@sp_user"].Value = s_userid;
                command.Parameters.Add("@sp_out", SqlDbType.VarChar, 50);
                command.Parameters["@sp_out"].Direction = ParameterDirection.Output;
                SqldbCenter.ExecuteNonQuery(command);

                if (command.Parameters["@sp_out"].Value.ToString().Trim() != "1")
                {
                    ls_middle = command.Parameters["@sp_out"].Value.ToString().Trim();
                }

                string sp_out = command.Parameters["@sp_out"].Value.ToString().Trim();
                LogBLL.Err("sp_out=" + sp_out);
                if (sp_out == "0")
                {
                    result = new
                    {
                        code = 200,
                        data = "操作成功 ",
                        sp_out = sp_out
                    };
                }
                else
                {
                    result = new
                    {
                        code = 500,
                        data = "操作失败： " + sp_out,
                        sp_out = sp_out
                    };
                }



            }
            catch (System.Exception ex)
            {
                result = new
                {
                    code = 500,
                    data = "查询失败: " + ex.Message, // "Query failed"
                    sp_out = ""

                };
            }
            finally
            {
                SqldbCenter.CloseConnection();
            }
            //return command.Parameters["@sp_out"].Value.ToString().Trim();

            return JsonConvert.SerializeObject(result);
        }
        #endregion



        //////////////////////////////////// 手持-移库管理  2024-10-31 add /////////////////////////////////////////////////////////

        /// <summary>
        /// 无任务移库确认
        /// </summary>
        /// <param name="s_org">组织ID</param>
        /// <param name="str_customer">货主</param>
        /// <param name="str_oloc">出盘点仓</param>
        /// <param name="tb_iloc">到盘点仓</param>
        /// <param name="tb_qty">数量</param>
        /// <param name="tb_pallet">托盘号（出）</param>
        /// <param name="tb_ipallet">托盘号（入）</param>
        /// <param name="str_userid">用户ID</param>
        /// <returns>结果</returns>
        [WebMethod]
        public string SQL_TM_f_timelymove_confirm(string s_org, string str_customer, string tb_sku, string str_oloc, string tb_iloc, string tb_qty, string tb_pallet, string tb_ipallet, string str_userid)
        {

            // 01.01.002.01   DC001
            // and a.standby_8 =  '2'  // 全部发货
            LogHelpter.AddLog("调用接口【SQL_TM_f_timelymove_confirm】 str_oloc=" + str_oloc + " ,tb_iloc=" + tb_iloc + " ,tb_qty=" + tb_qty + " ,str_userid=" + str_userid);
            LogHelpter.AddLog("[无任务移库][无任务移库确认][SQL_TM_f_timelymove_confirm] s_org=" + s_org + ",str_customer=" + str_customer + ",tb_sku=" + tb_sku + ",str_oloc=" + str_oloc + ",tb_iloc=" + tb_iloc + ",tb_qty=" + tb_qty + ",tb_pallet=" + tb_pallet + ",tb_ipallet=" + tb_ipallet + ",str_userid=" + str_userid);


            string str_sql, str_return, str_batch;
            DataSet ds_temp;
            decimal d_stock_qty;
            int i_temp;

            //判读是否有移入库位
            //判读是否有移入库位
            if (tb_iloc.Trim() != "")
            {

                if (Math.Round(System.Convert.ToDecimal(tb_qty.Trim()), 4) <= 0)
                {
                    /*MessageBox.Show(main.g_class.comm_getchangestring("The quantity is zero", main.g_info_dataset) + "!");
                    tb_qty.Focus();
                    return;*/
                    var result = new { code = 203, data = "数量为0", };
                    return JsonConvert.SerializeObject(result);

                }
                //判断是整版移动还是部分移动，如果是整版移动，托盘号带出来，如果是部分移动，托盘号清   
                if (tb_pallet.Trim() == "000000000000000") //说明是非托盘管理，则不要判断
                    tb_ipallet = tb_pallet.Trim();
                else //托盘管理,判断
                {
                    try
                    {
                        str_sql = "select sum(isnull(stock_qty_m,0)) stock_qty_m from wms.inv_sku_pallet where inv_org = '" + s_org + "' and pallet_no = '" + tb_pallet.Trim() + "'";
                        ds_temp = Sql_exec_selectsql(str_sql);

                        if (ds_temp.Tables[0].Rows.Count > 0)
                            d_stock_qty = System.Convert.ToDecimal(ds_temp.Tables[0].Rows[0]["stock_qty_m"].ToString().Trim());
                        else
                            d_stock_qty = 0;
                        if (tb_ipallet.Trim() != "")
                        {
                            //先判断是否是上架托盘
                            str_sql = "select count(*) from wms.asn_putaway where inv_org = '" + s_org + "' and pallet_no = '" + tb_ipallet.Trim() + "' and putaway_status = '0'";
                            i_temp = Sql_exec_selectint(str_sql);
                            if (i_temp > 0)  //托盘不合法 
                            {
                                var result = new { code = 203, data = "托盘" + tb_ipallet.Trim() + "正在入库", };
                                return JsonConvert.SerializeObject(result);

                            }
                            else
                            {
                                //在判断是否是空托盘
                                str_sql = "select count(*) from wms.inv_sku_pallet where inv_org = '" + s_org + "' and pallet_no = '" + tb_ipallet.Trim() + "' and isnull(stock_qty_m,0) > 0";
                                i_temp = Sql_exec_selectint(str_sql);

                                if (i_temp > 0) //说明托盘在库存中
                                {
                                    //则判断是否在扫描的库位里面
                                    str_sql = "select count(*) from wms.inv_sku_pallet where inv_org = '" + s_org + "' and pallet_no = '" + tb_ipallet.Trim() + "' and location_code <> '" + tb_iloc.Trim() + "' and isnull(stock_qty_m,0) > 0";
                                    i_temp = Sql_exec_selectint(str_sql);

                                    if (i_temp > 0) //说明托盘不合法
                                    {
                                        var result = new { code = 203, data = "托盘" + tb_ipallet.Trim() + "不在库位" + tb_iloc.Trim(), };
                                        return JsonConvert.SerializeObject(result);


                                    }
                                }
                            }
                        }
                        else
                        {
                            if (System.Convert.ToDecimal(tb_qty.Trim()) != d_stock_qty)
                            {
                                tb_ipallet = DateTime.Now.ToString("yyyyMMddHHmmss");
                            }
                            else
                            {
                                tb_ipallet = tb_pallet.Trim();
                            }
                        }
                    }
                    catch (System.Exception ee)
                    {
                        var result = new { code = 500, data = "移库确认失败 :" + ee.Message.ToString(), };
                        return JsonConvert.SerializeObject(result);

                        //MessageBox.Show(ee.Message.ToString());
                        //return;
                    }
                }
                //////////判断库位是否合法
                ////////try
                ////////{
                ////////    if (main.dbtype == "SQL")
                ////////        str_return = main.dbservice.Sql_exec_proc_checkloc(main.g_org, str_customer, tb_sku.Text.Trim(), str_oloc, tb_iloc.Text.Trim(), Math.Round(System.Convert.ToDecimal(tb_qty.Text.Trim()), 4), str_pack);
                ////////    else
                ////////        str_return = main.dbservice.oracle_exec_proc_checkloc(main.g_org, str_customer, tb_sku.Text.Trim(), str_oloc, tb_iloc.Text.Trim(), Math.Round(System.Convert.ToDecimal(tb_qty.Text.Trim()), 4), str_pack);
                ////////    if (str_return == "UNKNOWN") //说明不合法
                ////////    {
                ////////        MessageBox.Show(main.g_class.comm_getchangestring("The location is illegal", main.g_info_dataset) + "!");
                ////////        return;
                ////////    }
                ////////}
                ////////catch (System.Exception ee)
                ////////{
                ////////    MessageBox.Show(main.g_class.comm_getchangestring("The location is illegal", main.g_info_dataset) + "!" + ee.Message.ToString());
                ////////    return;
                ////////}

                int d_qty = string.IsNullOrEmpty(tb_qty.Trim()) ? 0 : Convert.ToInt32(tb_qty.Trim());

                str_batch = "";
                try
                {
                    //if (Menbox() > 0) //箱集管理
                    //{
                    //    //得到一个移库单据号
                    //    if (mono == "")
                    //    {
                    //        mono = main.g_class.comm_getaccountno("MOVE_NO");
                    //    }
                    //    if (dt_box.Rows.Count <= 0)
                    //    {
                    //        MessageBox.Show("请扫描箱标签!");
                    //        return;
                    //    }
                    //    else //判断数量是否合法
                    //    {
                    //        decimal d_qty_m;
                    //        string str_str;
                    //        string[] str_multi_sql = new string[2];
                    //        //判断数量是否合法
                    //        d_qty_m = 0;
                    //        str_str = "";
                    //        for (int ii = 0; ii < dt_box.Rows.Count; ii++)
                    //        {
                    //            d_qty_m = d_qty_m + System.Convert.ToDecimal(dt_box.Rows[ii]["qty_m"].ToString().Trim());
                    //            str_str = str_str + "'" + dt_box.Rows[ii]["box_tag"].ToString().Trim() + "',";
                    //        }
                    //        if (str_str != "")
                    //        {
                    //            str_str = str_str.Substring(0, str_str.Length - 1);
                    //        }
                    //        if (System.Convert.ToDecimal(tb_qty.Trim()) != d_qty_m) //数量不合法不能确认
                    //        {
                    //            var result = new
                    //            {
                    //                code = 202,
                    //                data = "移动数量:" + tb_qty.Trim() + ",确认数量:" + d_qty_m.ToString(),
                    //            };
                    //            return JsonConvert.SerializeObject(result);

                    //            //MessageBox.Show("移动数量:" + tb_qty.Trim() + ",确认数量:" + d_qty_m.ToString());
                    //            //return;
                    //        }
                    //        else
                    //        {
                    //            //更新库存箱级标签的信息
                    //            //把已经作标志的清空,然后对新的箱标做上标志
                    //            str_multi_sql[0] = "update " + main.g_space + ".inv_sku_rfid set so_no = '',so_line_no=0 where inv_org = '" + main.g_org + "' and consignor = '" + str_customer + "' and sku = '" + tb_sku.Text.Trim() + "'" +
                    //                          " and location_code = '" + tb_loc.Text.Trim() + "' and pallet_no = '" + tb_pallet.Text.Trim() + "'" +
                    //                          " and so_no = '" + mono.Trim() + "' and so_line_no = 0";
                    //            str_multi_sql[1] = "update " + main.g_space + ".inv_sku_rfid set so_no = '" + mono.Trim() + "',so_line_no= 0 where inv_org = '" + main.g_org + "' and consignor = '" +
                    //                          str_customer + "' and sku = '" + tb_sku.Text.Trim() + "' and location_code = '" + tb_loc.Text.Trim() + "' and pallet_no = '" +
                    //                          tb_pallet.Text.Trim() + "' and box_rfid in (" + str_str + ")";
                    //            try
                    //            {
                    //                Sql_exec_mutilsql(str_multi_sql);
                    //            }
                    //            catch (System.Exception ee)
                    //            {
                    //                MessageBox.Show(ee.Message.ToString());
                    //                return;
                    //            }
                    //        }
                    //    }
                    //    //调用webservice 
                    //    str_return = main.dbservice.Sql_exec_proc_boxtime_mv(s_inv, mono, str_customer, tb_sku.Text.Trim(), str_oloc, tb_pallet.Text.Trim(), str_batch, tb_iloc.Text.Trim(), tb_ipallet.Text.Trim(), Math.Round(System.Convert.ToDecimal(tb_qty.Text.Trim()), 4), "00", "1", main.g_user);
                    //    mono = "";
                    //}
                    //else
                    //{
                    str_return = Sql_TM_exec_proc_timelymove(s_org, str_customer, tb_sku.Trim(), str_oloc, tb_pallet.Trim(), str_batch, tb_iloc.Trim(), tb_ipallet.Trim(), Math.Round(System.Convert.ToDecimal(tb_qty.Trim()), 4), "00", "1", str_userid);

                    //}
                    if (str_return.Trim() == "1") //说明成功
                    {
                        //成功后清空
                        //f_clear_all();

                        var result = new
                        {
                            code = 200,
                            data = "移库成功" ,
                        };
                        return JsonConvert.SerializeObject(result);
                    }
                    else
                    {
                        var result = new
                        {
                            code = 202,
                            data = "无任务移库失败!" + str_return.Trim(),
                        };
                        return JsonConvert.SerializeObject(result);

                        //MessageBox.Show("无任务移库失败!" + str_return.Trim());
                        //return;
                    }
                }
                catch (System.Exception ee)
                {
                    var result = new
                    {
                        code = 500,
                        data = "移库确认失败 :" + ee.Message.ToString(),
                    };
                    return JsonConvert.SerializeObject(result);

                    //MessageBox.Show(main.g_class.comm_getchangestring("Fail to confirm timely moving", main.g_info_dataset) + "!" + ee.Message.ToString());
                    //return;
                }

            }
            else
            {
                //MessageBox.Show(main.g_class.comm_getchangestring("Please input target location", main.g_info_dataset) + "!");
                //return;
                var result = new
                {
                    code = 204,
                    data = "请输入目标库位",

                };
                return JsonConvert.SerializeObject(result);
            }

        }

        //无任务移库确认(存储过程)
        [WebMethod]
        public string Sql_TM_exec_proc_timelymove(string s_org, string s_consignor, string s_sku, string s_oloc, string s_opallet, string s_batch, string s_iloc, string s_ipalle, decimal d_qty, string s_reason, string s_flag, string s_user)
        {
            LogHelpter.AddLog("调用接口【Sql_TM_exec_proc_timelymove】");
            LogHelpter.AddLog("[无任务移库][无任务移库确认][Sql_TM_exec_proc_timelymove] s_org=" + s_org + ",s_consignor=" + s_consignor + ",s_sku=" + s_sku + ",s_oloc=" + s_oloc + ",s_iloc=" + s_iloc + ",d_qty=" + d_qty + ",s_reason=" + s_reason + ",s_flag=" + s_flag + ",s_user=" + s_user);

            SqlCommand command;
            command = new SqlCommand("wms.SP_SQL_TIMELY_MOVE");
            SqlDatabaseController SqldbCenter = new SqlDatabaseController();
            try
            {
                SqldbCenter.OpenConnection();
                SqldbCenter.BeginTransaction();
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add("@sp_org", SqlDbType.Char, 15);
                command.Parameters["@sp_org"].Value = s_org;
                command.Parameters.Add("@sp_csgor", SqlDbType.Char, 15);
                command.Parameters["@sp_csgor"].Value = s_consignor;
                command.Parameters.Add("@sp_sku", SqlDbType.Char, 30);
                command.Parameters["@sp_sku"].Value = s_sku;
                command.Parameters.Add("@sp_oloc", SqlDbType.Char, 20);
                command.Parameters["@sp_oloc"].Value = s_oloc;
                command.Parameters.Add("@sp_opallet", SqlDbType.Char, 15);
                command.Parameters["@sp_opallet"].Value = s_opallet;
                command.Parameters.Add("@sp_batch", SqlDbType.Char, 12);
                command.Parameters["@sp_batch"].Value = s_batch;
                command.Parameters.Add("@sp_iloc", SqlDbType.Char, 20);
                command.Parameters["@sp_iloc"].Value = s_iloc;
                command.Parameters.Add("@sp_ipallet", SqlDbType.Char, 15);
                command.Parameters["@sp_ipallet"].Value = s_ipalle;
                command.Parameters.Add("@sp_qty", SqlDbType.Decimal);
                command.Parameters["@sp_qty"].Precision = (byte)16;
                command.Parameters["@sp_qty"].Scale = (byte)4;
                command.Parameters["@sp_qty"].Value = d_qty;
                command.Parameters.Add("@sp_reason", SqlDbType.Char, 2);
                command.Parameters["@sp_reason"].Value = s_reason;
                command.Parameters.Add("@sp_flag", SqlDbType.Char, 1);
                command.Parameters["@sp_flag"].Value = s_flag;
                command.Parameters.Add("@sp_type", SqlDbType.Char, 1);
                command.Parameters["@sp_type"].Value = "0";
                command.Parameters.Add("@sp_fork", SqlDbType.Char, 4);
                command.Parameters["@sp_fork"].Value = "RF";
                command.Parameters.Add("@sp_user", SqlDbType.Char, 10);
                command.Parameters["@sp_user"].Value = s_user;
                command.Parameters.Add("@sp_out", SqlDbType.VarChar, 256);
                command.Parameters["@sp_out"].Direction = ParameterDirection.Output;
                SqldbCenter.ExecuteNonQuery(command);
                if (command.Parameters["@sp_out"].Value.ToString().Trim() == "1")
                {
                    SqldbCenter.Commit();
                }
                else
                {
                    SqldbCenter.Rollback();
                }
            }
            catch (System.Exception)
            {
                SqldbCenter.Rollback();
                throw;
            }
            finally
            {
                SqldbCenter.CloseConnection();
            }
            return command.Parameters["@sp_out"].Value.ToString().Trim();
        }

        //判断无任务移库的库位是否有效
        [WebMethod]
        public string Sql_TM_exec_proc_checkloc(string s_org, string s_consignor, string s_sku, string s_oloc, string s_iloc, decimal d_qty, string s_pack)
        {
            LogHelpter.AddLog("调用接口【Sql_TM_exec_proc_checkloc】");
            LogHelpter.AddLog("[无任务移库][库位是否有效][Sql_TM_exec_proc_checkloc] s_org=" + s_org + ",s_consignor=" + s_consignor + ",s_sku=" + s_sku + ",s_oloc=" + s_oloc + ",s_iloc=" + s_iloc + ",d_qty=" + d_qty + ",s_pack=" + s_pack);

            SqlCommand command;
            command = new SqlCommand("wms.SP_SQL_CHECK_LOC");
            SqlDatabaseController SqldbCenter = new SqlDatabaseController();
            try
            {
                SqldbCenter.OpenConnection();
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add("@sp_org", SqlDbType.Char, 15);
                command.Parameters["@sp_org"].Value = s_org;
                command.Parameters.Add("@sp_csgor", SqlDbType.Char, 15);
                command.Parameters["@sp_csgor"].Value = s_consignor;
                command.Parameters.Add("@sp_sku", SqlDbType.Char, 30);
                command.Parameters["@sp_sku"].Value = s_sku;
                command.Parameters.Add("@sp_outloc", SqlDbType.Char, 20);
                command.Parameters["@sp_outloc"].Value = s_oloc;
                command.Parameters.Add("@sp_inloc", SqlDbType.Char, 20);
                command.Parameters["@sp_inloc"].Value = s_iloc;
                command.Parameters.Add("@sp_qty", SqlDbType.Decimal);
                command.Parameters["@sp_qty"].Precision = (byte)16;
                command.Parameters["@sp_qty"].Scale = (byte)4;
                command.Parameters["@sp_qty"].Value = d_qty;
                command.Parameters.Add("@sp_pack", SqlDbType.Char, 20);
                command.Parameters["@sp_pack"].Value = s_pack;
                command.Parameters.Add("@sp_putloc", SqlDbType.VarChar, 50);
                command.Parameters["@sp_putloc"].Direction = ParameterDirection.Output;
                SqldbCenter.ExecuteNonQuery(command);
            }
            catch (System.Exception)
            {
                throw;
            }
            finally
            {
                SqldbCenter.CloseConnection();
            }
            return command.Parameters["@sp_putloc"].Value.ToString().Trim();
        }


        /// <summary>
        /// 得到相应商品的信息
        /// </summary>
        /// <param name="s_org">组织ID</param>
        /// <param name="str_customer">货主</param>
        /// <param name="str_oloc">盘点仓（出）</param>
        /// <param name="tb_pallet">托盘号</param>
        /// <returns></returns>
        [WebMethod]
        public string SQL_TM_f_get_sku(string s_org, string str_customer, string str_oloc, string tb_pallet)
        {
            LogHelpter.AddLog("调用接口【SQL_TM_f_get_sku】");
            LogHelpter.AddLog("[无任务移库][商品信息][SQL_TM_f_get_sku] s_org=" + s_org + ",str_customer=" + str_customer + ",str_oloc=" + str_oloc + ",tb_pallet=" + tb_pallet);

            string str_sql, s_num;
            DataSet ds_temp;
            try
            {
                str_sql = "select a.sku,b.sku_name,sum(isnull(a.stock_qty_m,0)) stock_qty_m from wms.inv_sku_pallet a, wms.mst_sku b where a.inv_org = b.inv_org and a.consignor = b.consignor and a.sku = b.sku " +
                              " and a.inv_org = '" + s_org + "' and a.consignor = '" + str_customer + "' and a.location_code = '" + str_oloc + "' and a.pallet_no = '" + tb_pallet.Trim() + "' group by a.sku,b.sku_name order by a.sku,b.sku_name";
                ds_temp = Sql_exec_selectsql(str_sql);

                if (ds_temp.Tables[0].Rows.Count > 0) //说明有数据，供选择
                {

                    var result = new
                    {
                        code = 200,
                        data = "查询成功",
                        items = ds_temp
                    };
                    return JsonConvert.SerializeObject(result);

                }
                else
                {
                    var result = new
                    {
                        code = 202,
                        data = "未查询到符合条件的记录 :",
                    };
                    return JsonConvert.SerializeObject(result);
                }
            }
            catch (System.Exception ee)
            {
                var result = new
                {
                    code = 500,
                    data = "查询商品信息失败 :" + ee.Message.ToString().Trim(),
                };
                return JsonConvert.SerializeObject(result);

            }
        }

        /// <summary>
        /// 根据sku得到相应的信息
        /// </summary>
        /// <param name="s_org">组织ID</param>
        /// <param name="str_customer">货主</param>
        /// <param name="str_query">商品码或货位</param>
        /// <param name="type">查询方式 1.按商品条码查询，2.按货位查询</param>
        /// <returns></returns>
        [WebMethod]
        public string SQL_TM_f_get_sku_information(string s_org, string str_query,string type)
        {
            LogHelpter.AddLog("调用接口【SQL_TM_f_get_sku_information】");

            string str_sql, s_num;
            int i_row;
            DataSet ds_loc, ds_sku, ds_stock;
            decimal d_stockm, d_usem1, d_usem2;



            //根据商品查找相应的名称和包装
            try
            {

                
                //得到相应商品的库存和可用库存
                str_sql =
                " select  * from ( " +

                " select  distinct " +
                " a.inv_org, a.batchno,"+
                " a.location_code,a.pallet_no,a.consignor," +// -- 库位、托盘号、货主编码
                " a.sku ,b.sku_name,b.barcode,d.barcode_list,b.main_unit,c.qty02 as box_spec," +
                " b.fix_location, " + // -- A位
                " a.stock_qty_m, " +                                                                    // -- 库存数量
                " a.stock_qty_m + a.ungot_qty_m - a.allocate_qty_m - a.freeze_qty_m as pallet_ky_qty_m,   " +          // -- 可用数量(库存+预收货-已分配-冻结)h.stock_qty_m - h.moveout_qty_m -h.replenishout_qty_m - h.freeze_qty_m - h.allocate_qty_m
                " case WHEN ((a.stock_qty_m + a.ungot_qty_m - a.allocate_qty_m - a.freeze_qty_m) < (e.stock_qty_m - e.moveout_qty_m -e.replenishout_qty_m - e.freeze_qty_m - e.allocate_qty_m)) " +
                " then (a.stock_qty_m + a.ungot_qty_m - a.allocate_qty_m - a.freeze_qty_m) else (e.stock_qty_m - e.moveout_qty_m -e.replenishout_qty_m - e.freeze_qty_m - e.allocate_qty_m) end as ky_qty_m, " + // -- 可用数量(对比托盘和location的，取最小值)
                " e.stock_qty_m as loc_stock_qty_m, " +                                                 // -- 库位库存数
                " e.stock_qty_m - e.moveout_qty_m -e.replenishout_qty_m - e.freeze_qty_m - e.allocate_qty_m as loc_ky_qty_m " + // -- 库位可用数量(库存+预收货-已分配-冻结)
                " from wms.inv_sku_pallet a " +
                " left join wms.mst_sku b on a.inv_org = b.inv_org and a.consignor = b.consignor and a.sku = b.sku" +
                " left JOIN WMS.mst_pack c ON a.pack_code = c.pack_code AND a.sku = c.sku " +
                " left join wms.view_mst_sku_barcode d on   a.sku = d.sku" +
                " left join wms.inv_sku_location e on   a.inv_org = e.inv_org and a.consignor = e.consignor and a.sku = e.sku and a.location_code = e.location_code " +
                " where 1=1 " +
                " and a.inv_org = '" + s_org + "' " +
                "  ";

                // 查询商品码
                if (type == "1")
                {
                    //str_sql += " and b.barcode = '" + str_query + "' ";
                    str_sql += " and (a.sku = '" + str_query + "' or d.barcode_list  like '%" + str_query + "%') "; 
                }
                // 查询库位
                if (type == "2")
                {
                    str_sql += " and a.location_code = '" + str_query + "' ";
                }

                str_sql += " ) a where a. ky_qty_m <> 0 "; // 过滤可用调库存为0的，不显示
                

                str_sql += "order by a.location_code,a.sku";
                

                ds_stock = Sql_exec_selectsql(str_sql);
                if (ds_stock.Tables[0].Rows.Count > 0) //说明有数据
                {
                    //string inv_org = ds_stock.Tables[0].Rows[0]["inv_org"].ToString().Trim();
                    //string batchno = ds_stock.Tables[0].Rows[0]["batchno"].ToString().Trim();
                    //string sku = ds_stock.Tables[0].Rows[0]["sku"].ToString().Trim();
                    //string sku_name = ds_stock.Tables[0].Rows[0]["sku_name"].ToString().Trim();
                    //string location_code = ds_stock.Tables[0].Rows[0]["location_code"].ToString().Trim();
                    //string consignor = ds_stock.Tables[0].Rows[0]["consignor"].ToString().Trim();
                    //string main_unit = ds_stock.Tables[0].Rows[0]["main_unit"].ToString().Trim();
                    //string box_spec = ds_stock.Tables[0].Rows[0]["box_spec"].ToString().Trim();
                    //string stock_qty_m = ds_stock.Tables[0].Rows[0]["stock_qty_m"].ToString().Trim();
                    //string ky_qty_m = ds_stock.Tables[0].Rows[0]["ky_qty_m"].ToString().Trim();

                    DataTable dt = ds_stock.Tables[0];


                    //d_stockm = Convert.ToDecimal(ds_stock.Tables[0].Rows[0]["stock_qty_m"].ToString().Trim());

                    var result = new
                    {
                        code = 200,
                        data = "查询成功",
                        items = dt
                    };
                    return JsonConvert.SerializeObject(result);

                }
                else
                {
                    var result = new
                    {
                        code = 202,
                        data = "未查询到符合条件的记录 :",
                    };
                    return JsonConvert.SerializeObject(result);
                }

            }
            catch (System.Exception ee)
            {
                var result = new
                {
                    code = 500,
                    data = "查询信息失败 :" + ee.Message.ToString().Trim(),
                };
                return JsonConvert.SerializeObject(result);

                //MessageBox.Show(main.g_class.comm_getchangestring("Fail to get Sku's Information", main.g_info_dataset) + "!" + ee.ToString());
                //return;
            }

        }

        /// <summary>
        /// 根据条码得到商品相应的信息
        /// </summary>
        /// <param name="s_org">组织ID</param>
        /// <param name="s_consignor">货主编码</param>
        /// <param name="str_barcode">条形码</param>
        /// <returns></returns>
        [WebMethod]
        public string SQL_TM_getSkuInfoByBarcode(string s_org, string s_consignor, string str_barcode)
        {
            LogHelpter.AddLog("调用接口【SQL_TM_getSkuInfoByBarcode】");
            string str_sql, s_num;
            int i_row;
            DataSet ds_loc, ds_sku, ds_stock;
            decimal d_stockm, d_usem1, d_usem2;



            //根据商品查找相应的名称和包装
            try
            {


                //得到相应商品的库存和可用库存
                str_sql =
                " select " +
                " a.inv_org, a.consignor," +
                " a.sku ,a.sku_name,a.barcode,a.spec,a.main_unit,a.pack_code,c.qty02 as box_spec,a.fix_location " +
                " from wms.mst_sku a " +
                " left JOIN WMS.mst_pack c ON a.pack_code = c.pack_code AND a.sku = c.sku " +
                " where 1=1 " +
                " and a.inv_org = '" + s_org + "' " +
                " and a.consignor = '" + s_consignor + "' " +
                " and a.barcode = '" + str_barcode + "' " +
                "  ";



                ds_stock = Sql_exec_selectsql(str_sql);
                if (ds_stock.Tables[0].Rows.Count > 0) //说明有数据
                {
                    //string inv_org = ds_stock.Tables[0].Rows[0]["inv_org"].ToString().Trim();
                    //string batchno = ds_stock.Tables[0].Rows[0]["batchno"].ToString().Trim();
                    //string sku = ds_stock.Tables[0].Rows[0]["sku"].ToString().Trim();
                    //string sku_name = ds_stock.Tables[0].Rows[0]["sku_name"].ToString().Trim();
                    //string location_code = ds_stock.Tables[0].Rows[0]["location_code"].ToString().Trim();
                    //string consignor = ds_stock.Tables[0].Rows[0]["consignor"].ToString().Trim();
                    //string main_unit = ds_stock.Tables[0].Rows[0]["main_unit"].ToString().Trim();
                    //string box_spec = ds_stock.Tables[0].Rows[0]["box_spec"].ToString().Trim();
                    //string stock_qty_m = ds_stock.Tables[0].Rows[0]["stock_qty_m"].ToString().Trim();
                    //string ky_qty_m = ds_stock.Tables[0].Rows[0]["ky_qty_m"].ToString().Trim();

                    DataTable dt = ds_stock.Tables[0];

                    var result = new
                    {
                        code = 200,
                        data = "查询成功",
                        items = dt
                    };
                    return JsonConvert.SerializeObject(result);

                }
                else
                {
                    var result = new
                    {
                        code = 202,
                        data = "未查询到符合条件的记录 :",
                    };
                    return JsonConvert.SerializeObject(result);
                }

            }
            catch (System.Exception ee)
            {
                var result = new
                {
                    code = 500,
                    data = "查询信息失败 :" + ee.Message.ToString().Trim(),
                };
                return JsonConvert.SerializeObject(result);

                //MessageBox.Show(main.g_class.comm_getchangestring("Fail to get Sku's Information", main.g_info_dataset) + "!" + ee.ToString());
                //return;
            }

        }

        /// <summary>
        /// 根据库位得到仓库相应的信息
        /// </summary>
        /// <param name="s_org">组织ID</param>
        /// <param name="s_location_code">库位编码</param>
        /// <returns></returns>
        [WebMethod]
        public string SQL_TM_getWarehouseInfoByLoc(string s_org, string s_location_code)
        {
            LogHelpter.AddLog("调用接口【SQL_TM_getWarehouseInfoByLoc】");
            string str_sql, s_num;
            int i_row;
            DataSet ds_loc, ds_sku, ds_stock;
            decimal d_stockm, d_usem1, d_usem2;



            //根据商品查找相应的名称和包装
            try
            {


                //得到相应商品的库存和可用库存
                str_sql =
                " select " +
                " a.inv_org, a.location_code," +
                " b.area_code ,b.area_name,c.warehouse,c.warehouse_name " +
                " from wms.mst_location a " +
                " inner JOIN WMS.mst_area b ON a.inv_org = b.inv_org AND a.area_code = b.area_code " +
                " inner JOIN WMS.mst_warehouse c ON b.inv_org = c.inv_org AND b.warehouse = c.warehouse " +
                " where 1=1 " +
                " and a.inv_org = '" + s_org + "' " +
                " and a.location_code = '" + s_location_code + "' " +
                "  ";



                ds_stock = Sql_exec_selectsql(str_sql);
                if (ds_stock.Tables[0].Rows.Count > 0) //说明有数据
                {
                    //string inv_org = ds_stock.Tables[0].Rows[0]["inv_org"].ToString().Trim();
                    //string batchno = ds_stock.Tables[0].Rows[0]["batchno"].ToString().Trim();
                    //string sku = ds_stock.Tables[0].Rows[0]["sku"].ToString().Trim();
                    //string sku_name = ds_stock.Tables[0].Rows[0]["sku_name"].ToString().Trim();
                    //string location_code = ds_stock.Tables[0].Rows[0]["location_code"].ToString().Trim();
                    //string consignor = ds_stock.Tables[0].Rows[0]["consignor"].ToString().Trim();
                    //string main_unit = ds_stock.Tables[0].Rows[0]["main_unit"].ToString().Trim();
                    //string box_spec = ds_stock.Tables[0].Rows[0]["box_spec"].ToString().Trim();
                    //string stock_qty_m = ds_stock.Tables[0].Rows[0]["stock_qty_m"].ToString().Trim();
                    //string ky_qty_m = ds_stock.Tables[0].Rows[0]["ky_qty_m"].ToString().Trim();

                    DataTable dt = ds_stock.Tables[0];

                    var result = new
                    {
                        code = 200,
                        data = "查询成功",
                        items = dt
                    };
                    return JsonConvert.SerializeObject(result);

                }
                else
                {
                    var result = new
                    {
                        code = 202,
                        data = "未查询到符合条件的记录 :",
                    };
                    return JsonConvert.SerializeObject(result);
                }

            }
            catch (System.Exception ee)
            {
                var result = new
                {
                    code = 500,
                    data = "查询信息失败 :" + ee.Message.ToString().Trim(),
                };
                return JsonConvert.SerializeObject(result);

                //MessageBox.Show(main.g_class.comm_getchangestring("Fail to get Sku's Information", main.g_info_dataset) + "!" + ee.ToString());
                //return;
            }

        }

        //////////////////////////////////// 手持-盘点管理  2024-12-09 add /////////////////////////////////////////////////////////

        /// <summary>
        /// 盘点库位
        /// </summary>
        /// <param name="s_org">组织ID</param>
        /// <param name="str_check_no">盘点号</param>
        /// <returns></returns>
        [WebMethod]
        public string SQL_INVCHECK_f_getLocationCodeByCheckNo(string s_org, string str_check_no)
        {
            LogHelpter.AddLog("调用接口【SQL_INVCHECK_f_getLocationCodeByCheckNo】");
            LogHelpter.AddLog("[盘点][盘点库位][SQL_INVCHECK_f_getLocationCodeByCheckNo] s_org=" + s_org + ",str_check_no=" + str_check_no);
            string str_sql;
            DataSet ds_inv;



            //根据商品查找相应的名称和包装
            try
            {

                //得到相应商品的库存和可用库存
                str_sql =
                " select  " +
                " distinct ltrim(rtrim(location_code)) location_code " +
                " from WMS.inv_check_detail " +
                " where 1=1 " +
                " and inv_org = '" + s_org + "' " +
                " and check_no = '" + str_check_no + "' " +
                " order by location_code ";


                ds_inv = Sql_exec_selectsql(str_sql);
                if (ds_inv.Tables[0].Rows.Count > 0) //说明有数据
                {

                    DataTable dt = ds_inv.Tables[0];
                    var result = new
                    {
                        code = 200,
                        data = "查询成功",
                        items = dt
                    };
                    return JsonConvert.SerializeObject(result);

                }
                else
                {
                    var result = new
                    {
                        code = 202,
                        data = "未查询到符合条件的记录 :",
                    };
                    return JsonConvert.SerializeObject(result);
                }

            }
            catch (System.Exception ee)
            {
                var result = new
                {
                    code = 500,
                    data = "查询信息失败 :" + ee.Message.ToString().Trim(),
                };
                return JsonConvert.SerializeObject(result);

            }

        }

        /// <summary>
        /// 根据条码得到商品相应的信息
        /// </summary>
        /// <param name="s_org">组织ID</param>
        /// <param name="s_consignor">货主编码</param>
        /// <param name="str_barcode">条形码</param>
        /// <returns></returns>
        [WebMethod]
        public string SQL_INVCHECK_f_getSkuInfo(string s_org, string s_consignor, string str_barcode)
        {
            LogHelpter.AddLog("调用接口【SQL_INVCHECK_f_getSkuInfo】");
            LogHelpter.AddLog("[盘点][查询条码商品][SQL_INVCHECK_f_getSkuInfo] s_org=" + s_org + ",s_consignor=" + s_consignor + ",str_barcode=" + str_barcode);

            string str_sql, s_num;
            int i_row;
            DataSet ds_loc, ds_sku, ds_stock;
            decimal d_stockm, d_usem1, d_usem2;



            //根据商品查找相应的名称和包装
            try
            {


                //得到相应商品的库存和可用库存
                str_sql =
                " select " +
                " a.inv_org, a.consignor," +
                " a.sku ,a.sku_name,b.barcode,a.spec,a.main_unit,a.pack_code,c.qty02 as box_spec,a.fix_location " +
                " from wms.mst_sku a " +
                " JOIN WMS.mst_sku_barcode b ON a.sku = b.sku" +
                " left JOIN WMS.mst_pack c ON a.pack_code = c.pack_code AND a.sku = c.sku " +
                " where 1=1 " +
                " and a.inv_org = '" + s_org + "' " +
                " and a.consignor = '" + s_consignor + "' " +
                " and (b.sku = '" + str_barcode + "' or b.barcode = '" + str_barcode + "' ) " +
                "  ";



                ds_stock = Sql_exec_selectsql(str_sql);
                if (ds_stock.Tables[0].Rows.Count > 0) //说明有数据
                {
                    //string inv_org = ds_stock.Tables[0].Rows[0]["inv_org"].ToString().Trim();
                    //string batchno = ds_stock.Tables[0].Rows[0]["batchno"].ToString().Trim();
                    //string sku = ds_stock.Tables[0].Rows[0]["sku"].ToString().Trim();
                    //string sku_name = ds_stock.Tables[0].Rows[0]["sku_name"].ToString().Trim();
                    //string location_code = ds_stock.Tables[0].Rows[0]["location_code"].ToString().Trim();
                    //string consignor = ds_stock.Tables[0].Rows[0]["consignor"].ToString().Trim();
                    //string main_unit = ds_stock.Tables[0].Rows[0]["main_unit"].ToString().Trim();
                    //string box_spec = ds_stock.Tables[0].Rows[0]["box_spec"].ToString().Trim();
                    //string stock_qty_m = ds_stock.Tables[0].Rows[0]["stock_qty_m"].ToString().Trim();
                    //string ky_qty_m = ds_stock.Tables[0].Rows[0]["ky_qty_m"].ToString().Trim();

                    DataTable dt = ds_stock.Tables[0];

                    var result = new
                    {
                        code = 200,
                        data = "查询成功",
                        items = dt
                    };
                    return JsonConvert.SerializeObject(result);

                }
                else
                {
                    var result = new
                    {
                        code = 202,
                        data = "未查询到符合条件的记录 :",
                    };
                    return JsonConvert.SerializeObject(result);
                }

            }
            catch (System.Exception ee)
            {
                var result = new
                {
                    code = 500,
                    data = "查询信息失败 :" + ee.Message.ToString().Trim(),
                };
                return JsonConvert.SerializeObject(result);

                //MessageBox.Show(main.g_class.comm_getchangestring("Fail to get Sku's Information", main.g_info_dataset) + "!" + ee.ToString());
                //return;
            }

        }

        /// <summary>
        /// 盘点明细
        /// </summary>
        /// <param name="s_org">组织ID</param>
        /// <param name="str_check_no">盘点号</param>
        /// <param name="str_location_code_start">开始库位号</param>
        /// <param name="str_location_code_end">结束库位号</param>
        /// <returns></returns>
        [WebMethod]
        public string SQL_INVCHECK_f_getInvCheckDetailInfo(string s_org, string str_check_no, string str_location_code_start)
        {
            LogHelpter.AddLog("调用接口【SQL_INVCHECK_f_getInvCheckDetailInfo】");
            LogHelpter.AddLog("[盘点][盘点明细][SQL_INVCHECK_f_getInvCheckDetailInfo] s_org=" + s_org + ",str_check_no=" + str_check_no + ",str_location_code_start=" + str_location_code_start);

            string str_sql;
            DataSet ds_inv;

            //根据商品查找相应的名称和包装
            try
            {

                //得到相应商品的库存和可用库存
                str_sql =
                " select  " +
                " check_no, check_line_no, pallet_no,consignor, " +
                " sku, sku_name, stock_qty_m, stock_qty_s, " +
                " location_code, check_qty_m, check_qty_s, check_status, " +
                " batchno, '0' lscheck, check_day, checker, " +
                " updater, update_time, main_dist, inv_org  " +
                " from WMS.inv_check_detail " +
                " where 1=1 " +
                " and inv_org = '" + s_org + "' " +
                " and check_no = '" + str_check_no + "' " +
                " and location_code like '" + str_location_code_start + "%' " +
                // " and location_code <= '" + str_location_code_end + "' " +
                " and check_status IN ( '0', '1' ) " +
                " order by location_code ";

                ds_inv = Sql_exec_selectsql(str_sql);
                if (ds_inv.Tables[0].Rows.Count > 0) //说明有数据
                {

                    DataTable dt = ds_inv.Tables[0];
                    var result = new
                    {
                        code = 200,
                        data = "查询成功",
                        items = dt
                    };
                    return JsonConvert.SerializeObject(result);

                }
                else
                {
                    var result = new
                    {
                        code = 202,
                        data = "未查询到符合条件的记录 :",
                    };
                    return JsonConvert.SerializeObject(result);
                }

            }
            catch (System.Exception ee)
            {
                var result = new
                {
                    code = 500,
                    data = "查询信息失败 :" + ee.Message.ToString().Trim(),
                };
                return JsonConvert.SerializeObject(result);

            }

        }

        /// <summary>
        /// 盘点明细（按库位和单品码）
        /// </summary>
        /// <param name="s_org">组织ID</param>
        /// <param name="str_check_no">盘点号</param>
        /// <param name="str_location_code_start">开始库位号</param>
        /// <returns></returns>
        [WebMethod]
        public string SQL_INVCHECK_f_getInvCheckDetailInfoGroupByLocSku(string s_org, string str_check_no, string str_location_code_start)
        {
            LogHelpter.AddLog("调用接口【SQL_INVCHECK_f_getInvCheckDetailInfoGroupByLocSku】");
            LogHelpter.AddLog("[盘点][盘点明细（按库位和单品码）][SQL_INVCHECK_f_getInvCheckDetailInfoGroupByLocSku] s_org=" + s_org + ",str_check_no=" + str_check_no + ",str_location_code_start=" + str_location_code_start);

            string str_sql;
            DataSet ds_inv;

            //根据商品查找相应的名称和包装
            try
            {

                //得到相应商品的库存和可用库存
                str_sql =
                " select   " +
                " a.inv_org,a.consignor,  " +
                " a.check_no, a.location_code, " +
                " a.sku, a.sku_name,  " +
                " ms.main_unit, "+
                " ms.barcode,vmsb.barcode_list," +
                " mp.qty02 as box_spec,  " +
                " sum(a.stock_qty_m) stock_qty_m,   " +
                " sum(a.check_qty_m) check_qty_m,   " +
                " a.check_status,  " +
                " (CASE a.check_status when '0' THEN '未确认' when '1' THEN '确认' ELSE '' END) check_status_name   " +
                " from WMS.inv_check_detail a  " +
                " LEFT JOIN WMS.mst_sku  ms ON a.inv_org = ms.inv_org AND a.consignor = ms.consignor AND a.sku = ms.sku  " +
                " LEFT JOIN WMS.mst_pack mp ON a.inv_org = mp.inv_org AND a.consignor = mp.consignor AND a.sku = mp.sku " +
                " LEFT JOIN WMS.VIEW_mst_sku_barcode vmsb ON a.sku = vmsb.sku " +
                " where 1=1 " +
                " and a.inv_org = '" + s_org + "' " +
                " and a.check_no = '" + str_check_no + "' " +
                " and a.location_code like '" + str_location_code_start + "%' " +
                    // " and location_code <= '" + str_location_code_end + "' " +
                " and a.check_status IN ( '0', '1' ) " +
                " group by a.inv_org,a.consignor, a.check_no, a.location_code, a.sku, a.sku_name,ms.main_unit,ms.barcode,vmsb.barcode_list, mp.qty02,  a.check_status " +
                " order by a.location_code ";

                ds_inv = Sql_exec_selectsql(str_sql);
                if (ds_inv.Tables[0].Rows.Count > 0) //说明有数据
                {

                    DataTable dt = ds_inv.Tables[0];
                    var result = new
                    {
                        code = 200,
                        data = "查询成功",
                        items = dt
                    };
                    return JsonConvert.SerializeObject(result);

                }
                else
                {
                    var result = new
                    {
                        code = 202,
                        data = "未查询到符合条件的记录 :",
                    };
                    return JsonConvert.SerializeObject(result);
                }

            }
            catch (System.Exception ee)
            {
                var result = new
                {
                    code = 500,
                    data = "查询信息失败 :" + ee.Message.ToString().Trim(),
                };
                return JsonConvert.SerializeObject(result);

            }

        }

        /// <summary>
        /// 获取盘点号
        /// </summary>
        /// <param name="s_org">组织ID</param>
        /// <returns></returns>
        [WebMethod]
        public string SQL_INVCHECK_f_getCheckNoList(string s_org,string s_check_no)
        {
            LogHelpter.AddLog("调用接口【SQL_INVCHECK_f_getCheckNoList】");
            LogHelpter.AddLog("[盘点][获取盘点号][SQL_INVCHECK_f_getCheckNoList] s_org=" + s_org + ",s_check_no=" + s_check_no);

            string str_sql;
            DataSet ds_inv;

            //根据商品查找相应的名称和包装
            try
            {

                //得到相应商品的库存和可用库存 check_type 1 标准盘点  2 动态盘点 3循环盘点
                str_sql =
                " select check_no,check_day,check_status,"+
                " (CASE check_status when '0' THEN '未确认' when '1' THEN '确认' ELSE '' END) check_status_name , " +
                " check_type,(CASE check_type when '1' THEN '标准盘点' when '2' THEN '动态盘点'  when '3' THEN '循环盘点'  ELSE '' END) check_type_name,"+
                " check_flag,(CASE check_flag when '0' THEN '货主' when '1' THEN '仓库'  when '2' THEN '区域' when '3' THEN '库位'  when '4' THEN '批号'  when '5' THEN '商品' ELSE '' END) check_flag_name from wms.inv_check_header " +
                " where inv_org = '" + s_org + "' and check_no like '%" + s_check_no + "%' and check_status in ('1','2') order by check_no"; ;

                ds_inv = Sql_exec_selectsql(str_sql);
                if (ds_inv.Tables[0].Rows.Count > 0) //说明有数据
                {

                    DataTable dt = ds_inv.Tables[0];
                    var result = new
                    {
                        code = 200,
                        data = "查询成功",
                        items = dt
                    };
                    return JsonConvert.SerializeObject(result);

                }
                else
                {
                    var result = new
                    {
                        code = 202,
                        data = "未查询到符合条件的记录 :",
                    };
                    return JsonConvert.SerializeObject(result);
                }

            }
            catch (System.Exception ee)
            {
                var result = new
                {
                    code = 500,
                    data = "查询信息失败 :" + ee.Message.ToString().Trim(),
                };
                return JsonConvert.SerializeObject(result);

            }

        }

        /// <summary>
        /// 新增盘点明细行
        /// </summary>
        /// <param name="s_org">组织ID</param>
        /// <param name="str_check_no">盘点号</param>
        /// <param name="s_consignor">货主编码</param>
        /// <param name="s_warehouse">仓库编码</param>
        /// <param name="s_area_code">区域编码</param>
        /// <param name="s_location_code">库位编码</param>
        /// <param name="s_sku">商品编码</param>
        /// <param name="i_stock_qty_m">库存数量</param>
        /// <param name="i_check_qty_m">实盘数量</param>
        /// <returns></returns>
        [WebMethod]
        public string SQL_INVCHECK_f_addDetail(string s_org, string str_check_no, string s_consignor, string s_warehouse, string s_area_code, string s_location_code, string s_sku, int i_stock_qty_m, int i_check_qty_m, string s_lot_attribute1, string s_user)
        {
            LogHelpter.AddLog("调用接口【SQL_INVCHECK_f_addDetail】");
            LogHelpter.AddLog("[盘点][新增盘点明细行][SQL_INVCHECK_f_addDetail] s_org=" + s_org + ",str_check_no=" + str_check_no + ",s_consignor=" + s_consignor + ",s_warehouse=" + s_warehouse + ",s_area_code=" + s_area_code + ",s_location_code=" + s_location_code + ",s_sku=" + s_sku + ",i_stock_qty_m=" + i_stock_qty_m + ",i_check_qty_m=" + i_check_qty_m + ",s_lot_attribute1=" + s_lot_attribute1 + ",s_user=" + s_user);

            string s_sku_name = "";
            string s_pack_code = "";
            decimal s_net_weight ;
            //string s_lot_attribute1 = "";                                     // 生产日期
            string s_lot_attribute2 = DateTime.Now.ToString("yyyy.MM.dd ");     // 收货日
            string s_lot_attribute3 = "非保税";                                 // 非保税 
            string s_lot_attribute4 = "";
            string s_lot_attribute5 = "";
            string s_lot_attribute6 = "";
            string s_lot_attribute7 = "";
            string s_lot_attribute8 = "";
            string s_lot_attribute9 = "";
            string s_lot_attribute10 = "";
            string s_lot_attribute11 = "";
            string s_lot_attribute12 = "";
            string s_lot_attribute13 = "";
            string s_lot_attribute14 = "";
            string s_lot_attribute15 = "";

            string str_sql;
            DataSet ds_inv;
            DataSet ds_sku_s;
            DataSet ds_pan;
            DataSet ds_pand;

            DataSet ds_skulocation;
            int i_stock_qty_m_old = 0;
            int i_stock_qty_s_old = 0;


            var result = new
            {
                code = 202,
                data = "无参数",
            };

            string str_out = "";
            SqlCommand command;
            command = new SqlCommand();
            SqlDatabaseController SqldbCenter = new SqlDatabaseController();
            
            //根据商品查找相应的名称和包装
            try
            {

                if (s_consignor.ToString().Trim() == null)
                {
                    return JsonConvert.SerializeObject(new { code = 202, data = "货主编码不能为空", });
                }
                else if (s_consignor.ToString().Trim() == "")
                {
                    return JsonConvert.SerializeObject(new { code = 202, data = "货主编码不能为空", });
                }
                //else if (s_warehouse.ToString().Trim() == "")
                //{
                //    return JsonConvert.SerializeObject(new { code = 202, data = "仓库编码不能为空", });
                //}
                else if (s_area_code.ToString().Trim() == "")
                {
                    return JsonConvert.SerializeObject(new { code = 202, data = "区域编码不能为空", });
                }
                else if (s_location_code.ToString().Trim() == "")
                {
                    return JsonConvert.SerializeObject(new { code = 202, data = "库位编码不能为空", });
                }
                else if (s_sku.ToString().Trim() == "")
                {
                    return JsonConvert.SerializeObject(new { code = 202, data = "商品编码不能为空", });
                }
                else if (i_stock_qty_m == null)
                {
                    return JsonConvert.SerializeObject(new { code = 202, data = "库存数量不能为空", });
                }
                else if (i_check_qty_m == null)
                {
                    return JsonConvert.SerializeObject(new { code = 202, data = "实盘数量不能为空", });
                }

                string s_usql3 = "select location_code from WMS.mst_location where area_code='" + s_area_code + "'  and location_code='" + s_location_code + "' and  inv_org = '" + s_org + "' ";
                ds_pan = Sql_exec_selectsql(s_usql3);
                if (ds_pan.Tables[0].Rows.Count > 0)
                {

                    //dataGridView1.Rows[e.RowIndex].Cells["location_code"].Value = s_pandian;
                }
                else
                {
                    return JsonConvert.SerializeObject(new { code = 202, data = "库位不正确！", });
                }

                // 得到实时库存
                //string s_usqllo = "select ISNULL(stock_qty_m,0) stock_qty_m ,ISNULL(stock_qty_s,0) stock_qty_s from WMS.inv_sku_location where inv_org = '" + s_org + "' and consignor='" + s_consignor + "' and sku='" + s_sku + "'  and location_code='" + s_location_code + "'  ";
                //ds_skulocation = Sql_exec_selectsql(s_usqllo);
                //if (ds_skulocation.Tables[0].Rows.Count > 0)
                //{
                //    i_stock_qty_m_old = Convert.ToInt32(ds_skulocation.Tables[0].Rows[0]["stock_qty_m"].ToString());
                //    i_stock_qty_s_old = Convert.ToInt32(ds_skulocation.Tables[0].Rows[0]["stock_qty_s"].ToString());
                //    //dataGridView1.Rows[e.RowIndex].Cells["location_code"].Value = s_pandian;
                //}


                //得到仓库编码
                string s_usql4 = "select warehouse from WMS.mst_area where 1=1 and  inv_org = '" + s_org + "' and area_code='" + s_area_code + "' ";
                s_warehouse = Sql_exec_selectstring(s_usql4);


                string sqlrr1 = "select sku_name,isnull(net_weight,0) net_weight,pack_code from WMS.mst_sku   where inv_org='" + s_org + "' and sku='" + s_sku + "' and main_dist<>'D' and consignor='" + s_consignor + "'";
                ds_sku_s = Sql_exec_selectsql(sqlrr1);
                if (ds_sku_s.Tables[0].Rows.Count > 0)
                {
                    //string[] str_sqlr1 = new string[dataGridView1.Rows.Count - 1];
                    string[] str_sqlr1 = new string[0];
                    string str_sql11 = "select max(check_line_no) as check_line_no from WMS.inv_check_detail where inv_org='" + s_org + "' and check_no='" + str_check_no + "'and consignor='" + s_consignor + "'";

                    ds_pand = Sql_exec_selectsql(str_sql11);

                    int pah = Convert.ToInt32(ds_pand.Tables[0].Rows[0]["check_line_no"].ToString());
                    //string s_check_no = ds_pand.Tables[0].Rows[0]["check_no"].ToString();
                    int pahh = pah;

                    
                        LogBLL.Err("0");

                        //验证是否为空
                        if (ds_sku_s.Tables[0].Rows[0]["sku_name"].ToString() != null)
                        {
                            s_sku_name = ds_sku_s.Tables[0].Rows[0]["sku_name"].ToString().Trim();
                        }
                        LogBLL.Err("01");
                        if (ds_sku_s.Tables[0].Rows[0]["net_weight"] != null)
                        {
                            s_net_weight = Convert.ToDecimal(ds_sku_s.Tables[0].Rows[0]["net_weight"].ToString().Trim());
                        }
                        if (ds_sku_s.Tables[0].Rows[0]["pack_code"].ToString() != null)
                        {
                            s_pack_code = ds_sku_s.Tables[0].Rows[0]["pack_code"].ToString().Trim();

                        }
                        //if (ds_sku_s.Tables[0].Rows[0]["lot_attribute1"].ToString() != null)
                        //{
                        //    s_lot_attribute1 = ds_sku_s.Tables[0].Rows[0]["lot_attribute1"].ToString().Trim();

                        //}
                        //if (ds_sku_s.Tables[0].Rows[0]["lot_attribute2"].ToString() != null)
                        //{
                        //    s_lot_attribute2 = ds_sku_s.Tables[0].Rows[0]["lot_attribute2"].ToString().Trim();

                        //}
                        //if (ds_sku_s.Tables[0].Rows[0]["lot_attribute3"].ToString() != null)
                        //{
                        //    s_lot_attribute3 = ds_sku_s.Tables[0].Rows[0]["lot_attribute3"].ToString().Trim();

                        //}
                        //if (ds_sku_s.Tables[0].Rows[0]["lot_attribute4"].ToString() != null)
                        //{
                        //    s_lot_attribute4 = ds_sku_s.Tables[0].Rows[0]["lot_attribute4"].ToString().Trim();

                        //}
                        //if (ds_sku_s.Tables[0].Rows[0]["lot_attribute5"].ToString() != null)
                        //{
                        //    s_lot_attribute5 = ds_sku_s.Tables[0].Rows[0]["lot_attribute5"].ToString().Trim();

                        //}
                        //if (ds_sku_s.Tables[0].Rows[0]["lot_attribute6"].ToString() != null)
                        //{
                        //    s_lot_attribute6 = ds_sku_s.Tables[0].Rows[0]["lot_attribute6"].ToString().Trim();

                        //}
                        //if (ds_sku_s.Tables[0].Rows[0]["lot_attribute7"].ToString() != null)
                        //{
                        //    s_lot_attribute7 = ds_sku_s.Tables[0].Rows[0]["lot_attribute7"].ToString().Trim();

                        //}
                        //if (ds_sku_s.Tables[0].Rows[0]["lot_attribute8"].ToString() != null)
                        //{
                        //    s_lot_attribute8 = ds_sku_s.Tables[0].Rows[0]["lot_attribute8"].ToString().Trim();

                        //}
                        //if (ds_sku_s.Tables[0].Rows[0]["lot_attribute9"].ToString() != null)
                        //{
                        //    s_lot_attribute9 = ds_sku_s.Tables[0].Rows[0]["lot_attribute9"].ToString().Trim();

                        //}
                        //if (ds_sku_s.Tables[0].Rows[0]["lot_attribute10"].ToString() != null)
                        //{
                        //    s_lot_attribute10 = ds_sku_s.Tables[0].Rows[0]["lot_attribute10"].ToString().Trim();

                        //}
                        //if (ds_sku_s.Tables[0].Rows[0]["lot_attribute11"].ToString() != null)
                        //{
                        //    s_lot_attribute11 = ds_sku_s.Tables[0].Rows[0]["lot_attribute11"].ToString().Trim();

                        //} if (ds_sku_s.Tables[0].Rows[0]["lot_attribute12"].ToString() != null)
                        //{
                        //    s_lot_attribute12 = ds_sku_s.Tables[0].Rows[0]["lot_attribute12"].ToString().Trim();

                        //}
                        //if (ds_sku_s.Tables[0].Rows[0]["lot_attribute13"].ToString() != null)
                        //{
                        //    s_lot_attribute13 = ds_sku_s.Tables[0].Rows[0]["lot_attribute13"].ToString().Trim();

                        //}
                        //if (ds_sku_s.Tables[0].Rows[0]["lot_attribute14"].ToString() != null)
                        //{
                        //    s_lot_attribute14 = ds_sku_s.Tables[0].Rows[0]["lot_attribute14"].ToString().Trim();

                        //}
                        //if (ds_sku_s.Tables[0].Rows[0]["lot_attribute15"].ToString() != null)
                        //{
                        //    s_lot_attribute15 = ds_sku_s.Tables[0].Rows[0]["lot_attribute15"].ToString().Trim();

                        //}




                        DataSet ds_st;
                        string sqltr = " select main_sub_flag,convert_modulus,shelflife_day,price,main_unit,isnull(cubage,0) cubage,isnull(net_weight,0) net_weight,isnull(gross_weight,0) gross_weight from wms.mst_sku  where sku='" + s_sku + "' and main_dist <>'D' and inv_org='" + s_org + "' and consignor='" + s_consignor + "'";
                        ds_st = Sql_exec_selectsql(sqltr);

                        string s_main_sub_fiag = ds_st.Tables[0].Rows[0]["main_sub_flag"].ToString().Trim();
                        decimal d_convert_modulus = Convert.ToDecimal(ds_st.Tables[0].Rows[0]["convert_modulus"].ToString().Trim());
                        int i_shelflife_day = Convert.ToInt32(ds_st.Tables[0].Rows[0]["shelflife_day"].ToString().Trim());
                        decimal d_price = Convert.ToDecimal(ds_st.Tables[0].Rows[0]["price"].ToString().Trim());
                        string s_main_unit = ds_st.Tables[0].Rows[0]["main_unit"].ToString().Trim();
                        decimal d_cubage = Convert.ToDecimal(ds_st.Tables[0].Rows[0]["cubage"].ToString().Trim());
                        decimal d_net_weight = Convert.ToDecimal(ds_st.Tables[0].Rows[0]["net_weight"].ToString().Trim());
                        decimal d_gross_weight = Convert.ToDecimal(ds_st.Tables[0].Rows[0]["gross_weight"].ToString().Trim());
                        pahh++;


                        str_sql = "insert into WMS.inv_check_detail (inv_org,check_no,check_line_no,consignor,batchno,warehouse,area_code,location_code," +
                            "pallet_no,sku,sku_name,pack_code,main_sub_flag,convert_modulus,stock_qty_m,stock_qty_s,ungot_qty_m,ungot_qty_s,allocate_qty_m," +
                            "allocate_qty_s,freeze_qty_m,freeze_qty_s,replenishin_qty_m,replenishin_qty_s,replenishout_qty_m,replenishout_qty_s,movein_qty_m," +
                            "movein_qty_s,moveout_qty_m,moveout_qty_s,check_qty_m,check_qty_s,shelflife_day,order_price,order_unit,cubage,gross_weight,net_weight," +
                            "lot_attribute1,lot_attribute2,lot_attribute3,lot_attribute4,lot_attribute5,lot_attribute6,lot_attribute7,lot_attribute8,lot_attribute9," +
                            "lot_attribute10,lot_attribute11,lot_attribute12,lot_attribute13,lot_attribute14,lot_attribute15,check_status,check_day,checker,updater,update_time,stock_qty_m_old,stock_qty_s_old,check_time" +
                            " ) values('" + s_org + "','" + str_check_no + "'," +
                            "'" + pahh + "','" + s_consignor + "','" + comm_getaccountno(s_org,"Batch_no") + "','" + s_warehouse + "','" + s_area_code + "','" + s_location_code + "','000000000000000','" + s_sku + "','" + s_sku_name + "','" + s_pack_code + "','" + s_main_sub_fiag + "'," +
                            "'" + d_convert_modulus + "','" + i_stock_qty_m + "','" + d_convert_modulus * i_stock_qty_m + "','0','0','0','0','0','0','0','0','0','0','0','0','0','0','" + i_check_qty_m + "','" + d_convert_modulus * i_check_qty_m + "','" + i_shelflife_day + "','" + d_price + "','" + s_main_unit + "'," +
                            "'" + d_cubage + "','" + i_check_qty_m * d_gross_weight + "','" + i_check_qty_m * d_net_weight + "','" + s_lot_attribute1 + "','" + s_lot_attribute2 + "','" + s_lot_attribute3 + "','" + s_lot_attribute4 + "','" + s_lot_attribute5 + "','" + s_lot_attribute6 + "','" + s_lot_attribute7 + "','" + s_lot_attribute8 + "','" + s_lot_attribute9 + "'," +
                            "'" + s_lot_attribute10 + "','" + s_lot_attribute11 + "','" + s_lot_attribute12 + "','" + s_lot_attribute13 + "','" + s_lot_attribute14 + "','" + s_lot_attribute15 + "','1','" + DateTime.Now.ToString("yyyy.MM.dd ") + "','" + s_user + "','" + s_user + "',GETDATE(),'" + i_stock_qty_m_old + "','" + i_stock_qty_s_old + "',GETDATE())";

                    LogBLL.Err("str_sql=" + str_sql);


                    //DataSet ds_inst;
                    //Sql_exec_mutilsql(str_sqlr1);


                    
                }
                else
                {
                    return JsonConvert.SerializeObject(new { code = 202, data = "商品编码输入有误", });
                }


                SqldbCenter.OpenConnection();
                SqldbCenter.BeginTransaction();

                // 插入新增
                command.CommandType = CommandType.Text;
                command.CommandText = str_sql;
                SqldbCenter.ExecuteScalar(command);

                // 盘点实数录入更新
                command.CommandType = CommandType.StoredProcedure;
                command.CommandText = "WMS.SP_SQL_INV_CHECK_INPUT";
                command.Parameters.Add("@sp_org", SqlDbType.Char, 15);
                command.Parameters["@sp_org"].Value = s_org;
                command.Parameters.Add("@sp_consignor", SqlDbType.Char, 15);
                command.Parameters["@sp_consignor"].Value = s_consignor;
                command.Parameters.Add("@sp_checkno", SqlDbType.Char, 30);
                command.Parameters["@sp_checkno"].Value = str_check_no;
                command.Parameters.Add("@sp_location", SqlDbType.Char, 30);
                command.Parameters["@sp_location"].Value = s_location_code;
                command.Parameters.Add("@sp_sku", SqlDbType.Char, 30);
                command.Parameters["@sp_sku"].Value = s_sku;
                command.Parameters.Add("@sp_qty", SqlDbType.Int);
                command.Parameters["@sp_qty"].Value = i_check_qty_m;
                command.Parameters.Add("@sp_check_user", SqlDbType.Char, 10);
                command.Parameters["@sp_check_user"].Value = s_user;
                command.Parameters.Add("@sp_out", SqlDbType.VarChar, 256);
                command.Parameters["@sp_out"].Direction = ParameterDirection.Output;
                SqldbCenter.ExecuteNonQuery(command);


                str_out = command.Parameters["@sp_out"].Value.ToString().Trim();

                if (str_out == "success")
                {
                    SqldbCenter.Commit();
                    result = new
                    {
                        code = 200,
                        data = "保存成功",
                    };
                }
                else
                {
                    SqldbCenter.Rollback();
                    result = new
                    {
                        code = 202,
                        data = "保存失败，错误信息：" + str_out,
                    };
                }

                //int cnt = dbmanagerSql.ExecuteNonQuerySql(connectionString, str_sql);
                //if (cnt > 0) //说明有数据
                //{

                //    var result = new
                //    {
                //        code = 200,
                //        data = "保存成功",
                //    };
                //    return JsonConvert.SerializeObject(result);

                //}
                //else
                //{
                //    var result = new
                //    {
                //        code = 202,
                //        data = "保存失败 :",
                //    };
                //    return JsonConvert.SerializeObject(result);
                //}

            }
            catch (System.Exception ex)
            {
                SqldbCenter.Rollback();
                result = new
                {
                    code = 500,
                    data = "保存异常，错误信息：" + ex.Message,
                };
            }
            finally
            {
                SqldbCenter.CloseConnection();
            }
            return JsonConvert.SerializeObject(result);

        }

        /// <summary>
        /// 更新盘点明细数量
        /// </summary>
        /// <param name="s_org">组织ID</param>
        /// <param name="str_check_no">盘点号</param>
        /// <param name="str_check_line_no">盘点行号</param>
        /// <param name="i_check_qty_m">数量</param>
        /// <returns></returns>
        [WebMethod]
        public string SQL_INVCHECK_f_updateCheckQty(string s_org, string str_check_no, string str_check_line_no, int i_check_qty_m, string s_user)
        {
            LogHelpter.AddLog("调用接口【SQL_INVCHECK_f_updateCheckQty】");
            LogHelpter.AddLog("[盘点][更新盘点明细数量][SQL_INVCHECK_f_updateCheckQty] s_org=" + s_org + ",str_check_no=" + str_check_no + ",str_check_line_no=" + str_check_line_no + ",i_check_qty_m=" + i_check_qty_m + ",s_user=" + s_user);

            string str_sql;
            DataSet ds_inv;

            //根据商品查找相应的名称和包装
            try
            {
                string[] str_end_sql = new string[2];
                List<string> sqlList = new List<string>();
                //得到相应商品的库存和可用库存
                str_end_sql[0] = "update wms.inv_check_header set check_status = '2',updater = '" + s_user + "',update_time = getdate() where inv_org = '" + s_org +
                                 "' and check_no = '" + str_check_no + "' and check_status= '1'";
                str_end_sql[1] = " update  t1 " +
                    "set t1.check_qty_m = '" + i_check_qty_m + "', t1.check_qty_s = '" + i_check_qty_m + "',t1.check_status = '1'," +
                    "t1.stock_qty_m = ISNULL(t2.stock_qty_m, 0) ,t1.stock_qty_s = ISNULL(t2.stock_qty_s, 0),check_time = getdate()," +
                    "t1.updater='" + s_user + "',t1.update_time = getdate() " +
                    " from WMS.inv_check_detail t1 " +
                    " left join WMS.inv_sku_pallet t2 on t1.inv_org = t2.inv_org and t1.batchno=t2.batchno and t1.location_code = t2.location_code and t1.pallet_no=t2.pallet_no  " +
                    " where t1.inv_org = '" + s_org + "' and t1.check_no='" + str_check_no + "' and t1.check_line_no='" + str_check_line_no + "'"; ;
                
                sqlList.Add(str_end_sql[0]);
                sqlList.Add(str_end_sql[1]);

                //str_sql = " update  WMS.inv_check_detail set check_qty_m = '" + i_check_qty_m + "', check_qty_s = '" + i_check_qty_m + "',check_status = '1' where inv_org = '" + s_org + "' and check_no='" + str_check_no + "' and check_line_no='" + str_check_line_no + "'"; ;
                // int cnt = dbmanagerSql.ExecuteNonQuerySql(connectionString, str_sql);
                if (OpDB.SaveDataToDB(connectionString, sqlList)) //说明有数据
                {

                    var result = new
                    {
                        code = 200,
                        data = "保存成功",
                    };
                    return JsonConvert.SerializeObject(result);

                }
                else
                {
                    var result = new
                    {
                        code = 202,
                        data = "保存失败 :",
                    };
                    return JsonConvert.SerializeObject(result);
                }

            }
            catch (System.Exception ee)
            {
                var result = new
                {
                    code = 500,
                    data = "保存失败 :" + ee.Message.ToString().Trim(),
                };
                return JsonConvert.SerializeObject(result);

            }

        }

        // 盘点实数录入更新(存储过程) (新盘点功能-按库位编码+单品码)
        [WebMethod]
        public string SQL_INVCHECK_exec_proc_inv_check_input(string s_org, string s_consignor, string s_checkno, string s_location, string s_sku, int i_qty, string s_user)
        {
            LogHelpter.AddLog("调用接口【SQL_INVCHECK_exec_proc_inv_check_input】");
            LogHelpter.AddLog("[盘点][盘点实数录入更新][SQL_INVCHECK_exec_proc_inv_check_input] s_org=" + s_org + ",s_consignor=" + s_consignor + ",s_checkno=" + s_checkno + ",s_location=" + s_location + ",s_sku=" + s_sku + ",i_qty=" + i_qty + ",s_user=" + s_user);

            string str_out = "";
            var result = new
            {
                code = 202,
                data = "无参数",
            };

            SqlCommand command;
            command = new SqlCommand("wms.SP_SQL_INV_CHECK_INPUT");
            SqlDatabaseController SqldbCenter = new SqlDatabaseController();
            try
            {
                SqldbCenter.OpenConnection();
                SqldbCenter.BeginTransaction();
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add("@sp_org", SqlDbType.Char, 15);
                command.Parameters["@sp_org"].Value = s_org;
                command.Parameters.Add("@sp_consignor", SqlDbType.Char, 15);
                command.Parameters["@sp_consignor"].Value = s_consignor;
                command.Parameters.Add("@sp_checkno", SqlDbType.Char, 30);
                command.Parameters["@sp_checkno"].Value = s_checkno;
                command.Parameters.Add("@sp_location", SqlDbType.Char, 30);
                command.Parameters["@sp_location"].Value = s_location;
                command.Parameters.Add("@sp_sku", SqlDbType.Char, 30);
                command.Parameters["@sp_sku"].Value = s_sku;
                command.Parameters.Add("@sp_qty", SqlDbType.Int);
                command.Parameters["@sp_qty"].Value = i_qty;
                command.Parameters.Add("@sp_check_user", SqlDbType.Char, 10);
                command.Parameters["@sp_check_user"].Value = s_user;
                command.Parameters.Add("@sp_out", SqlDbType.VarChar, 256);
                command.Parameters["@sp_out"].Direction = ParameterDirection.Output;
                SqldbCenter.ExecuteNonQuery(command);

                str_out = command.Parameters["@sp_out"].Value.ToString().Trim();

                if (str_out == "success")
                {
                    SqldbCenter.Commit();
                    result = new
                    {
                        code = 200,
                        data = "保存成功",
                    };
                }
                else
                {
                    SqldbCenter.Rollback();
                    result = new
                    {
                        code = 202,
                        data = "保存失败，错误信息：" + str_out,
                    };
                }
            }
            catch (System.Exception ex)
            {
                SqldbCenter.Rollback();
                result = new
                {
                    code = 500,
                    data = "保存异常，错误信息：" + ex.Message,
                };
            }
            finally
            {
                SqldbCenter.CloseConnection();
            }
            return JsonConvert.SerializeObject(result);
        }

        //手持盘点确认
        [WebMethod]
        public void SQL_INVCHECK_Sql_check_confirm(string s_org, DataSet ds_check, string strsql, string json, string s_user)
        {
            LogHelpter.AddLog("调用接口【SQL_INVCHECK_Sql_check_confirm】");
            LogHelpter.AddLog("[盘点][手持盘点确认][SQL_INVCHECK_Sql_check_confirm] s_org=" + s_org + ",strsql=" + strsql + ",json=" + json + ",s_user=" + s_user);

            string str_sql;
            string str_check;
            int i_count;



            DataSet dataSet = JsonConvert.DeserializeObject<DataSet>(json);
            // 如果JSON字符串不包含DataSet的完整结构（如没有明确的"Table"名称或没有"Tables"集合），则可能需要手动构建DataSet
            if (dataSet.Tables.Count == 0)
            {
                // 手动创建DataTable并添加到DataSet中
                DataTable dataTable = JsonConvert.DeserializeObject<DataTable>(json.Replace("Table", ""));
                dataSet.Tables.Add(dataTable);
            }

            // 现在dataSet包含了解析后的数据

            ds_check = dataSet;


            str_check = ds_check.Tables[0].Rows[0]["check_no"].ToString().Trim();
            SqlCommand command;
            command = new SqlCommand(strsql);
            SqlDatabaseController SqldbCenter = new SqlDatabaseController();
            try
            {
                SqldbCenter.OpenConnection();
                SqldbCenter.BeginTransaction();
                SqldbCenter.updatedatabaser(command, ds_check);
                str_sql = "select count(*) from wms.inv_check_detail where inv_org = '" + s_org + "' and check_no = '" + ds_check.Tables[0].Rows[0]["check_no"].ToString().Trim() + "' and check_status = '0'";
                command.CommandType = CommandType.Text;
                command.CommandText = str_sql;
                i_count = SqldbCenter.ExecuteScalar(command);
                if (i_count > 0) //说明没有全部确认完,部分确认
                {
                    str_sql = "update wms.inv_check_header set check_status = '2',check_day = convert(char(10),getdate(),102),checker='" + s_user + "',updater='" + s_user + "',update_time = getdate(),main_dist = 'C'" +
                              " where inv_org = '" + s_org + "' and check_no = '" + str_check + "'";
                }
                else
                {
                    str_sql = "update wms.inv_check_header set check_status = '3',check_day = convert(char(10),getdate(),102),checker='" + s_user + "',updater='" + s_user + "',update_time = getdate(),main_dist = 'C'" +
                              " where inv_org = '" + s_org + "' and check_no = '" + str_check + "'";
                }
                command.CommandText = str_sql;
                command.CommandType = CommandType.Text;
                SqldbCenter.ExecuteNonQuery(command);
                SqldbCenter.Commit();
            }
            catch (System.Exception)
            {
                SqldbCenter.Rollback();
                throw;
            }
            finally
            {
                SqldbCenter.CloseConnection();
            }
        }


        //得到相应的单据号
        public string comm_getaccountno(string s_org,string accounttype)
        {
            Int32 tmp_page;
            string str_return, str_sql, str_no;
            string str_memo;
            DataSet ds_no;
            try
            {
                tmp_page = Sql_get_accountno(s_org, accounttype);
                if (tmp_page == 0)//没有找到相应的单据号，可能档案里面没有管理
                {
                    return "error";
                }
                else
                {
                    str_no = tmp_page.ToString().PadLeft(4, '0');
                    str_return = tmp_page.ToString().PadLeft(8, '0');
                    accounttype = accounttype.Trim().ToUpper();
                    if (accounttype == "ADJUST_NO") //调整单
                    {
                        str_return = "AD" + str_return;
                    }
                    else if (accounttype == "ASN_NO" || accounttype == "ASN_NO_1") //入库单
                    {
                        str_sql = "select id_data,substring(convert(char(10),getdate(),112),3,6) as s_date from wms.Sys_Parameter where inv_org = '" + s_org + "' and id = 8107";
                        ds_no = Sql_exec_selectsql(str_sql);
                        if (ds_no.Tables[0].Rows.Count > 0)
                        {
                            if (ds_no.Tables[0].Rows[0]["id_data"].ToString().Trim() == "1") //说明用特定的格式字母+yymmddxxxx
                            {
                                str_sql = "select memo from wms.mst_account where inv_org = '" + s_org + "' and upper(bill_id) = '" + accounttype + "'";
                                str_memo = Sql_exec_selectstring(str_sql).Trim();
                                return str_memo.Trim() + ds_no.Tables[0].Rows[0]["s_date"].ToString().Trim() + str_no;
                            }
                        }
                        str_return = "AN" + str_return;
                    }
                    else if (accounttype == "CHECK_NO") //盘点单
                    {
                        str_return = "CK" + str_return;
                    }
                    else if (accounttype == "FREEZE_NO") //冻结单  
                    {
                        str_return = "FR" + str_return;
                    }
                    else if (accounttype == "MOVE_NO") //移库单
                    {
                        str_return = "MV" + str_return;
                    }
                    else if (accounttype == "SO_NO" || accounttype == "SO_NO_1") //出库单
                    {
                        str_sql = "select id_data,substring(convert(char(10),getdate(),112),3,6) as s_date from wms.Sys_Parameter where inv_org = '" + s_org + "' and id = 8108";
                        ds_no = Sql_exec_selectsql(str_sql);
                        if (ds_no.Tables[0].Rows.Count > 0)
                        {
                            if (ds_no.Tables[0].Rows[0]["id_data"].ToString().Trim() == "1") //说明用特定的格式字母+yymmddxxxx
                            {
                                str_sql = "select memo from wms.mst_account where inv_org = '" + s_org + "' and upper(bill_id) = '" + accounttype + "'";
                                str_memo = Sql_exec_selectstring(str_sql).Trim();
                                return str_memo.Trim() + ds_no.Tables[0].Rows[0]["s_date"].ToString().Trim() + str_no;
                            }
                        }
                        str_return = "SO" + str_return;
                    }
                    else if (accounttype == "TRANS_NO") //转移单trans_no  
                    {
                        str_return = "TR" + str_return;
                    }
                    else if (accounttype == "TK_NO") //临时领料单
                    {
                        str_return = "TK" + str_return;
                    }
                    else if (accounttype == "OUT_NO") //委外出库单
                    {
                        str_return = "DP" + str_return;
                    }
                    else if (accounttype == "IN_NO") //委外入库单  
                    {
                        str_return = "IN" + str_return;
                    }
                    else if (accounttype == "TN_NO") //退库入库单
                    {
                        str_return = "TN" + str_return;
                    }
                    else if (accounttype == "TO_NO") //退货出库单  
                    {
                        str_return = "TO" + str_return;
                    }
                    else
                    {
                        str_return = "00" + str_return;
                    }
                    return str_return;
                }

            }
            catch (System.Exception ee)
            {
                return "error" + ee.ToString().Trim();
            }
        }

        //////////////////////////////////// 自动补货 /////////////////////////////////////////////////////////

        /// <summary>
        /// 自动补货 - 返回补货单明细列表
        /// </summary>
        /// <param name="s_org">组织ID</param>
        /// <param name="s_consignor">货主</param>
        /// <param name="s_location">库位号</param>
        /// <returns></returns>
        [WebMethod]
        public string SQL_REPLEASUM_f_getBillnoDetailList(string s_org, string s_consignor, string s_location)
        {
            LogHelpter.AddLog("调用接口【SQL_REPLEASUM_f_getBillnoDetailList】");
            string str_sql;
            string str_sql1;
            string str_sql2;
            DataSet ds_inv;

            //根据商品查找相应的名称和包装
            try
            {

                //得到相应商品的库存和可用库存 check_type 1 标准盘点  2 动态盘点 3循环盘点
                str_sql =
                " select a.inv_org, a.consignor, a.move_no, a.move_line_no, a.moveout_location, a.moveout_pallet, a.batchno," +
                " a.from_warehouse, a.to_warehouse, a.sku, a.sku_name,e.qty02 as box_spec, a.pack_code, a.main_sub_flag, a.convert_modulus, a.movein_location, a.movein_pallet," +
                " a.stock_qty_m, a.move_qty_m, a.tmp_qty_m, a.confirm_qty_m, a.move_status, " +
                " (CASE a.move_status when '0' THEN '未移动' when '1' THEN '部分移动' when '2' THEN '移动完成' ELSE '' END) move_status_name " +
                " from wms.inv_move_detail_sum a " +
                " LEFT JOIN WMS.mst_pack e ON a.inv_org = e.inv_org AND a.consignor = e.consignor AND a.sku = e.sku " +
                " where a.inv_org = '" + s_org + "' and a.consignor = '" + s_consignor + "' and a.move_status in ('0','1') ";


                str_sql1 = str_sql + " and a.moveout_location like '%" + s_location + "%'  order by move_no"; ;
                str_sql2 = str_sql + " and a.movein_location like '%" + s_location + "%'  order by move_no"; ;

                // 先查询moveout_location有数据吗
                ds_inv = Sql_exec_selectsql(str_sql1);
                if (ds_inv.Tables[0].Rows.Count > 0) //说明有数据
                {

                    DataTable dt = ds_inv.Tables[0];
                    var result = new
                    {
                        code = 200,
                        data = "查询成功",
                        items = dt
                    };
                    return JsonConvert.SerializeObject(result);

                }
                else
                {
                    // 如果没有，查询movein_location
                    ds_inv = Sql_exec_selectsql(str_sql2);
                    if (ds_inv.Tables[0].Rows.Count > 0) //说明有数据
                    {

                        DataTable dt = ds_inv.Tables[0];
                        var result = new
                        {
                            code = 200,
                            data = "查询成功",
                            items = dt
                        };
                        return JsonConvert.SerializeObject(result);

                    }
                    else
                    {
                        var result = new
                        {
                            code = 202,
                            data = "未查询到符合条件的记录 :",
                        };
                        return JsonConvert.SerializeObject(result);
                    }
                }

            }
            catch (System.Exception ee)
            {
                var result = new
                {
                    code = 500,
                    data = "查询信息失败 :" + ee.Message.ToString().Trim(),
                };
                return JsonConvert.SerializeObject(result);

            }

        }


        /// <summary>
        /// 自动补货-更新移动数量
        /// </summary>
        /// <param name="s_org">组织ID</param>
        /// <param name="str_check_no">盘点号</param>
        /// <param name="str_check_line_no">盘点行号</param>
        /// <param name="i_check_qty_m">数量</param>
        /// <returns></returns>
        [WebMethod]
        public string SQL_REPLEASUM_f_updateMoveQty(string s_org, string s_consignor, string s_move_no, string s_move_line_no, string s_moveout_location,string s_moveout_pallet, string s_batchno, int i_tmp_qty_m, string s_user)
        {
            LogHelpter.AddLog("调用接口【SQL_REPLEASUM_f_updateMoveQty】");
            string str_sql;
            string errMsg;
            int i_sy_qty = 0;
            DataSet ds_inv;

            //根据商品查找相应的名称和包装
            try
            {
                string[] str_end_sql = new string[2];
                List<string> sqlList = new List<string>();

                LogHelpter.AddLog("SQL_REPLEASUM_f_updateMoveQty");
                string s_str = "";

                // 判断是否超收
                s_str = " select CONVERT(INT, isnull(a.tmp_qty_m,0)  + " + i_tmp_qty_m + " - isnull(a.move_qty_m,0) - isnull(e.qty02 ,0))   " +
                " from wms.inv_move_detail_sum a " +
                " LEFT JOIN WMS.mst_pack e ON a.inv_org = e.inv_org AND a.consignor = e.consignor AND a.sku = e.sku " +
                " where a.inv_org = '" + s_org + "'  " +
                    " and a.consignor = '" + s_consignor + "'" +
                    " and a.move_no = '" + s_move_no + "' " +
                    " and a.move_line_no = '" + s_move_line_no + "' " +
                    " and a.moveout_location = '" + s_moveout_location + "' " +
                    " and a.moveout_pallet = '" + s_moveout_pallet + "' " +
                    " and a.batchno = '" + s_batchno + "' ";


                LogHelpter.AddLog("s_str=" + s_str);

                //i_sy_qty = Sql_exec_selectint(s_str);
                Object obj = OpDB.GetObject(connectionString, s_str);
                if (obj != null )
                {
                    i_sy_qty = Convert.ToInt32(obj);
                }


                LogHelpter.AddLog("i_sy_qty=" + i_sy_qty);
                if (i_sy_qty > 0)
                {
                    errMsg = "当前商品超收数量为" + i_sy_qty + ",请重新输入数量";
                    var result = new
                    {
                        code = 202,
                        data = errMsg,
                    };
                    return JsonConvert.SerializeObject(result);
                }


                //得到相应商品的库存和可用库存
                str_end_sql[0] = "update wms.inv_move_detail_sum set tmp_qty_m = isnull(tmp_qty_m,0) + " + i_tmp_qty_m + ", move_status = '1',updater = '" + s_user + "',update_time = getdate() " + 
                    " where inv_org = '" + s_org + "'  "+
                    " and consignor = '" + s_consignor + "'" +
                    " and move_no = '" + s_move_no + "' " +
                    " and move_line_no = '" + s_move_line_no + "' " +
                    " and moveout_location = '" + s_moveout_location + "' " +
                    " and moveout_pallet = '" + s_moveout_pallet + "' " +
                    " and batchno = '" + s_batchno + "' " +
                    " and move_status in ('0','1')";

                // 判断收货是否完成
                str_end_sql[1] = "update wms.inv_move_detail_sum set confirm_qty_m = tmp_qty_m , move_status = '2',updater = '" + s_user + "',update_time = getdate() " +
                    " where inv_org = '" + s_org + "'  " +
                    " and consignor = '" + s_consignor + "'" +
                    " and move_no = '" + s_move_no + "' " +
                    " and move_line_no = '" + s_move_line_no + "' " +
                    " and moveout_location = '" + s_moveout_location + "' " +
                    " and moveout_pallet = '" + s_moveout_pallet + "' " +
                    " and batchno = '" + s_batchno + "' " +
                    " and tmp_qty_m >= move_qty_m" + 
                    " and move_status in ('1')";


                sqlList.Add(str_end_sql[0]);
                sqlList.Add(str_end_sql[1]);

                LogHelpter.AddLog("str_end_sql1=" + str_end_sql[0]);
                LogHelpter.AddLog("str_end_sql2=" + str_end_sql[1]);

                //str_sql = " update  WMS.inv_check_detail set check_qty_m = '" + i_check_qty_m + "', check_qty_s = '" + i_check_qty_m + "',check_status = '1' where inv_org = '" + s_org + "' and check_no='" + str_check_no + "' and check_line_no='" + str_check_line_no + "'"; ;
                // int cnt = dbmanagerSql.ExecuteNonQuerySql(connectionString, str_sql);
                if (OpDB.SaveDataToDB(connectionString, sqlList)) //说明有数据
                {

                    var result = new
                    {
                        code = 200,
                        data = "保存成功",
                    };
                    return JsonConvert.SerializeObject(result);

                }
                else
                {
                    var result = new
                    {
                        code = 202,
                        data = "保存失败 :",
                    };
                    return JsonConvert.SerializeObject(result);
                }

            }
            catch (System.Exception ee)
            {
                var result = new
                {
                    code = 500,
                    data = "保存失败 :" + ee.Message.ToString().Trim(),
                };
                return JsonConvert.SerializeObject(result);

            }

        }



        //////////////////////////////////// Sql Server数据库/////////////////////////////////////////////////////////
        #region 通用数据库处理函数
        //提取系统登录信息
        [WebMethod]
        public DataSet Sql_get_logininfor(string strsql)
        {
            SqlCommand command;
            DataSet ds;
            command = new SqlCommand(strsql);
            SqlDatabaseController SqldbCenter = new SqlDatabaseController();
            try
            {
                SqldbCenter.OpenConnection();
                ds = SqldbCenter.ExecuteQuery(command);
            }
            catch (System.Exception)
            {
                throw;
            }
            finally
            {
                SqldbCenter.CloseConnection();
            }
            return ds;
        }

        //执行通用select语句返回字符串
        [WebMethod]
        public string Sql_exec_selectstring(string strsql)
        {
            SqlCommand command;
            DataSet ds;
            command = new SqlCommand(strsql);
            SqlDatabaseController SqldbCenter = new SqlDatabaseController();
            try
            {
                SqldbCenter.OpenConnection();
                ds = SqldbCenter.ExecuteQuery(command);
            }
            catch (System.Exception)
            {
                throw;
            }
            finally
            {
                SqldbCenter.CloseConnection();
            }
            if (ds.Tables[0].Rows.Count > 0)
                return ds.Tables[0].Rows[0][0].ToString().Trim();
            else
                return "";
        }

        //执行通用select语句返回整型
        [WebMethod]
        public Int32 Sql_exec_selectint(string strsql)
        {
            SqlCommand command;
            DataSet ds;
            command = new SqlCommand(strsql);
            SqlDatabaseController SqldbCenter = new SqlDatabaseController();
            try
            {
                SqldbCenter.OpenConnection();
                ds = SqldbCenter.ExecuteQuery(command);
            }
            catch (System.Exception)
            {
                throw;
            }
            finally
            {
                SqldbCenter.CloseConnection();
            }
            if (ds.Tables[0].Rows.Count > 0)
                return System.Convert.ToInt32(ds.Tables[0].Rows[0][0].ToString().Trim());
            else
                return 0;
        }

        //执行通用select语句语句返回数据集
        [WebMethod]
        public DataSet Sql_exec_selectsql(string strsql)
        {
            SqlCommand command;
            DataSet ds;
            command = new SqlCommand(strsql);
            SqlDatabaseController SqldbCenter = new SqlDatabaseController();
            try
            {
                SqldbCenter.OpenConnection();
                ds = SqldbCenter.ExecuteQuery(command);
            }
            catch (System.Exception)
            {
                throw;
            }
            finally
            {
                SqldbCenter.CloseConnection();
            }
            return ds;
        }

        //执行通用select语句返回多个数据集
        [WebMethod]
        public DataSet Sql_exec_select_mutilsql(string[] str_array)
        {
            SqlCommand command;
            DataSet ds;
            DataSet ds_return;
            ds_return = new DataSet();
            command = new SqlCommand();
            SqlDatabaseController SqldbCenter = new SqlDatabaseController();
            try
            {
                SqldbCenter.OpenConnection();
                for (int i = 0; i < str_array.Length; i++)
                {
                    if (str_array[i].ToString().Trim() != "")
                    {
                        command.CommandType = CommandType.Text;
                        command.CommandText = str_array[i].ToString().Trim();
                        ds = SqldbCenter.ExecuteQuery(command);
                        ds_return.Tables.Add(ds.Tables[0]);
                        ds = null;
                    }
                }
            }
            catch (System.Exception)
            {
                throw;
            }
            finally
            {
                SqldbCenter.CloseConnection();
            }
            return ds_return;
        }


        //执行通用command语句
        [WebMethod]
        public void Sql_exec_commandsql(string strsql)
        {
            SqlCommand command;
            command = new SqlCommand(strsql);
            SqlDatabaseController SqldbCenter = new SqlDatabaseController();
            try
            {
                SqldbCenter.OpenConnection();
                SqldbCenter.ExecuteNonQuery(command);
            }
            catch (System.Exception)
            {
                throw;
            }
            finally
            {
                SqldbCenter.CloseConnection();
            }
        }

        //批量更新数据库
        [WebMethod]
        public void Sql_refresh_db(DataSet dtst, string strsql)
        {
            SqlCommand command;
            command = new SqlCommand(strsql);
            SqlDatabaseController SqldbCenter = new SqlDatabaseController();
            try
            {
                SqldbCenter.OpenConnection();
                SqldbCenter.updatedatabaser(command, dtst);
            }
            catch (System.Exception)
            {
                throw;
            }
            finally
            {
                SqldbCenter.CloseConnection();
            }
        }

        //获取托盘号
        [WebMethod]
        public Int32 Sql_get_accountno(string s_org, string straccount)
        {
            string s_str;
            Int32 r_int;
            string g_usespace = "wms";
            SqlCommand command;
            DataSet ds;
            command = new SqlCommand();
            ds = new DataSet();
            straccount = straccount.ToUpper();
            SqlDatabaseController SqldbCenter = new SqlDatabaseController();
            try
            {
                SqldbCenter.OpenConnection();
                SqldbCenter.BeginTransaction();
                //先锁账号表
                s_str = "update " + g_usespace + ".mst_account set page_no = page_no where inv_org = '" + s_org + "' and upper(bill_id) = '" + straccount + "'";
                command.CommandText = s_str;
                SqldbCenter.ExecuteNonQuery(command);
                //得到相应的帐号
                s_str = "select page_no from " + g_usespace + ".mst_account where inv_org = '" + s_org + "' and upper(bill_id) = '" + straccount + "'";
                command.CommandText = s_str;
                ds = SqldbCenter.ExecuteQuery(command);
                if (ds.Tables[0].Rows.Count > 0) //说明找到
                {
                    r_int = System.Convert.ToInt32(ds.Tables[0].Rows[0][0].ToString().Trim());
                    r_int = r_int + 1;
                }
                else //没有找到
                {
                    r_int = 0;
                }
                //更新帐号表
                s_str = "update " + g_usespace + ".mst_account set page_no = page_no + 1 where inv_org = '" + s_org + "' and upper(bill_id) = '" + straccount + "'";
                command.CommandText = s_str;
                SqldbCenter.ExecuteNonQuery(command);
                SqldbCenter.Commit();
            }
            catch (System.Exception)
            {
                SqldbCenter.Rollback();
                throw;
            }
            finally
            {
                SqldbCenter.CloseConnection();
            }
            return r_int;
        }

        //批量更新数据库再加一条sql语句
        [WebMethod]
        public void Sql_insert_dataset(string str_insert, DataSet dtst, string strsql)
        {
            SqlCommand command;
            command = new SqlCommand();
            SqlDatabaseController SqldbCenter = new SqlDatabaseController();
            try
            {
                SqldbCenter.OpenConnection();
                SqldbCenter.BeginTransaction();
                command.CommandText = str_insert;
                SqldbCenter.ExecuteNonQuery(command);
                command.CommandText = strsql;
                SqldbCenter.updatedatabaser(command, dtst);
                SqldbCenter.Commit();
            }
            catch (System.Exception)
            {
                SqldbCenter.Rollback();
                throw;
            }
            finally
            {
                SqldbCenter.CloseConnection();
            }
        }

        //执行多条sql更新或者插入的语句
        [WebMethod]
        public void Sql_exec_mutilsql(string[] str_array)
        {
            SqlCommand command;
            command = new SqlCommand();
            SqlDatabaseController SqldbCenter = new SqlDatabaseController();
            try
            {
                SqldbCenter.OpenConnection();
                SqldbCenter.BeginTransaction();
                for (int i = 0; i < str_array.Length; i++)
                {
                    if (str_array[i].ToString().Trim() != "")
                    {
                        command.CommandText = str_array[i].ToString().Trim();
                        SqldbCenter.ExecuteNonQuery(command);
                    }
                }
                SqldbCenter.Commit();
            }
            catch (System.Exception)
            {
                SqldbCenter.Rollback();
                throw;
            }
            finally
            {
                SqldbCenter.CloseConnection();
            }
        }

        #endregion

    }

}
